YZlat
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks for your help
ASKER
Also is there any difference between dropping then purging and dropping and purging in one statement? I don't see any...