Data Pump job fails with error – ORA-31634: job already exists


Today when I want to export schema with data pump, I encountered with an error ORA-31634: job already exists. I queried dba_datapump_jobs table and saw that it has 99 jobs with the NOT RUNNING state.

SELECT owner_name,
job_name,
operation,
job_mode,
state
FROM dba_datapump_jobs;

When we are not using job name for data pump job, Oracle generates default name to the job and in Oracle data pump can generate up to 99 unique jobs. When job name already exists or you are running many expdp jobs at the same time ( more than 99 jobs), then data pump cannot generate a unique name and you get this error. Another reason why this problem occurs is when jobs are aborted, or when KEEP_MASTER=y used for the data pump the records stay there.

There are two solutions to this problem:

The first solution is dropping this orphaned tables, use result of the query below to drop tables

SELECT 'DROP table ' || owner_name || '.' || job_name || ';'
FROM DBA_DATAPUMP_JOBS
WHERE STATE = 'NOT RUNNING';
DROP TABLE DP_USER.SYS_EXPORT_SCHEMA_01 PURGE;
…
...
DROP TABLE DP_USER.SYS_EXPORT_SCHEMA_99 PURGE; 

2) The second solution is to use unique job name in data pump jobs like below:

expdp dp_user/password directory=DP_DIR dumpfile=backup.dmp logfile=logfile.log job_name=your_unique_job_name schemas=schema_name

Now you can run data pump jobs without any problem

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s