ORA-31633: unable to create master table "SCOTT.JOB2"

   一： 在用datapump导出一个表时报如下错误

expdp scott/oracle job_name=job2 directory=EXPDPDUMP tables=scott.TEST exclude=statistics,index dumpfile=TEST.dmp LOGFILE=TEST.log

Export: Release 11.2.0.3.0 - Production on Wed Jul 9 15:04:28 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SCOTT.JOB2"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-00955: name is already used by an existing object

    二：环境信息

操作系统版本：

uname -a
AIX egisbdb1 1 6 00F8A6874C00

数据库版本11.2.0.3.5

 

   三：问题原因

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.

一个新的expdp/impdp job用的名字被一个已经停止了的datapump job所占用（When starting the new Data Pump job and using a system generated name, we check the names of existing Data Pump jobs in the dba_datapump_job in order to obtain a unique new system generated jobname.）

 

SQL> select owner_name, job_name, operation, job_mode,
  2         state, attached_sessions
  3  from   dba_datapump_jobs
  4  where  job_name not like 'BIN$%'
  5  order  by 1, 2;

OWNER_NAME JOB_NAME   OPERATION  JOB_MODE   STATE                          ATTACHED_SESSIONS
---------- ---------- ---------- ---------- ------------------------------ -----------------
SCOTT      JOB2       EXPORT     TABLE      NOT RUNNING                                    0

--果然如上所述，job2已经被一个not running 的job所占用

 

  四：解决方案

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

清除旧的job，或者给新的job选一个新的名字（不要和dba_datapump_jobs中名字有冲突）

1. Determine in SQL*Plus which Data Pump jobs exist in the database:

确定datapump job存在于数据库中

-- locate Data Pump jobs:

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
COL owner.object for a50select 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'.

确定job不是活跃的，status字段应该显示为‘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.

确认上面查到的'NOT RUNNING'的job不是别人临时暂停的，而是失败的任务

4. Determine in SQL*Plus the related master tables:

查看该任务的master表

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.:

删除已经'NOT RUNNING'并且不再会重新开始的任务

drop table scott.job2;

 

SQL> select * from dba_datapump_jobs;

OWNER_NAME JOB_NAME   OPERATION  JOB_MODE   STATE                    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ---------- ---------- ---------- -------------------- ---------- ----------------- -----------------
SCOTT      BIN$/b7u4b EXPORT     TABLE      NOT RUNNING                   0                 0                 0
           a1AhTgQ8Co
           YBD/EQ==$0

 

--可以看到job2已经被放入recyclebin中了

6.purge recyclebin; 或者purge dba_recyclebin;

 

SQL> select * from dba_datapump_jobs;

no rows selected

7.Re-run the query on dba_datapump_jobs and dba_objects (step 1 and 4). If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. E.g.:

 

CONNECT scott/tiger

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT');
   DBMS_DATAPUMP.STOP_JOB (h1);
END;
/

Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. Query the view user_datapump_jobs to check whether the job has been removed:

CONNECT scott/tiger

SELECT * FROM user_datapump_jobs;

 

8.Confirm that the job has been removed:

CONNECT / as sysdba 
SET lines 200  
COL owner_name FORMAT a10;  
COL job_name FORMAT a20  
COL state FORMAT a12  
COL operation LIKE state  
COL job_mode LIKE state  
COL owner.object for a50

-- locate Data Pump jobs:  

SELECT owner_name, job_name, rtrim(operation) "OPERATION", 
       rtrim(job_mode) "JOB_MODE", state, attached_sessions
  FROM dba_datapump_jobs
 WHERE job_name NOT LIKE 'BIN$%'
 ORDER BY 1,2;

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED 
---------- ------------------- --------- --------- ----------- -------- 
SCOTT      EXPDP_20051121      EXPORT    SCHEMA    EXECUTING          1 
SYSTEM     SYS_EXPORT_FULL_01  EXPORT    FULL      NOT RUNNING        0 

-- locate Data Pump 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; 

STATUS   OBJECT_ID OBJECT_TYPE  OWNER.OBJECT 
------- ---------- ------------ ------------------------- 
VALID        85283 TABLE        SCOTT.EXPDP_20051121 
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

 

 

 

##本文所述方案取自于MOS如下NOTE

DataPump Export Or Import Fails With Errors ORA-31633 ORA-6512 ORA-955 (Doc ID 556425.1)
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (Doc ID 336014.1)