?
Solved

Error ORA-31634: job already exists fix

Posted on 2014-01-21
7
Medium Priority
?
4,888 Views
Last Modified: 2014-01-22
I was running data pump full export and gotten an error:


ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted



I cheked the table dba_datapump_jobs and it has 99 jobs in there, so that's why my export is failing. I need to cleanup orphaned datapump jobs as per document: How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)


In my case all the jobs shown as NOT RUNNING and my query


SQL> SELECT o.status, o.object_id, o.object_type,
       o.owner||'.'||object_name "OWNER.OBJECT"
  FROM dba_objects o, dba_datapump_jobs j
 WHERE o.owner=j.owner_name AND o.object_name=j.job_name
   AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;  


returns something like this:

STATUS   OBJECT_ID OBJECT_TYPE         OWNER.OBJECT
------- ---------- ------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------
VALID       252275 TABLE               SYS.SYS_EXPORT_FULL_01
VALID       252297 TABLE               SYS.SYS_EXPORT_FULL_02
VALID       252319 TABLE               SYS.SYS_EXPORT_FULL_03
VALID       252439 TABLE               SYS.SYS_EXPORT_FULL_04
VALID       252557 TABLE               SYS.SYS_EXPORT_FULL_05
VALID       252675 TABLE               SYS.SYS_EXPORT_FULL_06

SO I wanted to drop


DROP TABLE SYS.SYS_EXPORT_FULL_01;

and then puge it from dba_recyclebin


but not sure it is safe to do. Can someone help me out and explain to me how can I determine which jobs are safe to drop?
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39798158
yes, you can do that, and for the purge, do it in the DROP directly:

DROP TABLE SYS.SYS_EXPORT_FULL_01 PURGE;
0
 
LVL 35

Author Comment

by:YZlat
ID: 39798222
should I drop them all or just some? Or would one be enough? There are 99 of them...

Also is there any difference between dropping then purging and dropping and purging in one statement? I don't see any...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39798228
yes, you can drop them all.
and there is no difference except that you can request the purge "immediately", per table vs the purge one all the tables are dropped.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Author Comment

by:YZlat
ID: 39798299
One last thing, I checked other databases on the server and none have any data in dba_datapump_jobs table, just one database. Can you tell me what causes this table to fill up? I want to prevent it in the future?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39798345
the reason the records stay there is when jobs are aborted, or when KEEP_MASTER=y  is used for the data pump
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39798743
I guess no other solution exists apart from cleaning up the left over tables/orphaned tables which are created by the expdp.

I hope you also had already fixed the issue by trying the above posted earlier updates.

you can also refer to these if needed :
http://oracledba-duniya.blogspot.sg/2013/05/resolving-ora-31634-job-already.html#!/2013/05/resolving-ora-31634-job-already.html
https://community.oracle.com/message/2319683
https://community.oracle.com/thread/2238420?tstart=0
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 39799885
Thanks for your help
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question