本文主要是介绍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存在于数据库中
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表
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'并且不再会重新开始的任务
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中了
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_sessionsFROM dba_datapump_jobsWHERE 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)
这篇关于ORA-31633: unable to create master table SCOTT.JOB2的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!