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
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
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.
Clear the old expdp job or specify a different jobname
1. Connect to SQL*Plus as 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;
Category: Background processing (500)
- Article last edited: 25-Oct-2017