Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

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

    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Problem Symptoms:
    Log says: 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;

    Category: Background processing (500)


    • Article last edited: 2/14/2015
    //doorbell.io feedback widged