DataPump Export Or Import Fails With Errors ORA-31633 ORA-6512 ORA-955

郑全

DataPump Export Or Import Fails
With Errors ORA-31633 ORA-6512 ORA-955 (文档 ID 556425.1)



In this Document

Symptoms

Cause

Solution

References

---

Applies to:  
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Nov-2015***

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 ?