Solved

Error ORA-31634: job already exists fix

Posted on 2014-01-21
7
3,840 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
  • 3
  • 3
7 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now