Link to home
Start Free TrialLog in
Avatar of YZlat
YZlatFlag for United States of America

asked on

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?
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YZlat

ASKER

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...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YZlat

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of YZlat

ASKER

Thanks for your help