Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Error ORA-31634: job already exists fix

Posted on 2014-01-21
7
Medium Priority
?
5,229 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

636 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