Data Pump: job does not exist； unable to create master table； name is already used ...

Article Type: General
Product: Aleph
Product Version: 20, 21, 22, 23

Problem Symptoms:
The log shows:

create_customer_data.log:ORA-31626: job does not exist
create_customer_data.log:ORA-31633: unable to create master table “ALEPH_ADMIN.ABC01_JOB”
create_customer_data.log:ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
create_customer_data.log:ORA-06512: at “SYS.KUPV$FT”, line 863
create_customer_data.log:ORA-00955: name is already used by an existing object

Cause:
The error is caused by a stopped job that remained in the dba_datapump_jobs. The new expdp job has the same name as the old expdp job.

Resolution:
Clear the old expdp job or specify a different jobname

1. Connect to SQL*Plus as ALEPH_ADMIN:
   sqlplus $ALEPH_ADMIN

2. Determine in SQL*Plus which Data Pump jobs exist in the database:
   – locate Data Pump jobs:
   SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE ‘BIN$%’
   ORDER BY 1,2;

3. Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be ‘NOT RUNNING’.

4. Check with the job owner that the job with status ‘NOT RUNNING’ in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed.

5. Determine in SQL*Plus the related master tables:
   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;

6. For jobs that were stopped in the past and won’t be restarted anymore, delete the master table. E.g.:
   DROP TABLE scott.sys_export_table_02;

Note: In the case of an Upgrade Express export which was getting this error, the following worked:

drop table ALEPH_ADMIN.ABC50_JOB;