Error ORA-31634: job already exists fix

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?
LVL 35
YZlatAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, you can do that, and for the purge, do it in the DROP directly:

DROP TABLE SYS.SYS_EXPORT_FULL_01 PURGE;
0
 
YZlatAuthor Commented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
YZlatAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the reason the records stay there is when jobs are aborted, or when KEEP_MASTER=y  is used for the data pump
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
YZlatAuthor Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.