DataPump Export or Import Fails With ORA-31633 ORA-6512 ORA-955 (Doc ID 556425.1)

Modified Date LabelModified

Modified Date02-SEP-2011

Document Type LabelType

Document TypePROBLEM

Status

PUBLISHED(EXTERNAL)

Priority

3

To Bottom

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.8 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 02-SEP-2011***

Symptoms

When exporting or importing using the DataPump utility, the following error messages appear:

ORA-31626: job does not exist
ORA-31633: unable to create master table "RMSPRD80.imp_refresh_MONTH_DATA_FM20081"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-00955: name is already used by an existing object

Cause

Error is caused by a stopped job that remained in the DBA_DATAPUMP_JOBS. The new expdp/impdp job has the same name as the old expdp/impdp job.

Solution

Clear the old job or specify a different name for the new job.

1. 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;

2. Ensure that the listed jobs in DBA_DATAPUMP_JOBS are not active DataPump export/import jobs. The status should be 'NOT RUNNING'.

3. Check with the job owner that the job with status 'NOT RUNNING' in DBA_DATAPUMP_JOBS is not an export/import DataPump job that has been temporary stopped, but is actually a job that failed.

4. 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;

5. 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;

References

NOTE:336014.1 - How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

ITPub博客

DataPump Export or Import Fails With ORA-31633 ORA-6512 ORA-955