ORA-31634: job already exists;ORA-31664: unable to construct unique job name when defaulted

ORA-31634: job already exists;ORA-31664: unable to construct unique job name when defaulted

In the database which I support have a daily export backup and one day the logs reported with the errors

ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted

Reason for the Error:

This error normally occurs when you do not specify a unique JOB_NAME for the DataPump job  and for some reason that JOB_NAME already exists in the database, or else if you are running many jobs at the same time (more than 99 jobs) and DataPump cannot create a new job.

Resolution:

To fix this issue you have to cleanup the orphaned datapump jobs.

1) Determine the datapump jobs which exists in the database and the status as NOT RUNNING.

SELECT owner_name, job_name, operation, job_mode, 

   state, attached_sessions 

   FROM dba_datapump_jobs 

   where owner_name='&SCHEMA' and state='NOT RUNNING'

ORDER BY 1,2; 

2) Determine the related master tables

SELECT o.status, o.object_id, o.object_type, 

          o.owner||'.'||object_name "OWNER.OBJECT",o.created,j.state 

     FROM dba_objects o, dba_datapump_jobs j 

    WHERE o.owner=j.owner_name AND o.object_name=j.job_name 

   and o.owner='&SCHEMA' and j.state='NOT RUNNING' ORDER BY 4,2;

3) Drop the tables generated in step 2.

Drop table ;

This will clear the master tables created by orphaned jobs