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