0. summary
1. 问题现象
2. 问题处理
. 2.1 查找在数据库中有哪些数据泵作业
. 2.2 查找相关的master表
. 2.3 对于过去被终止的和根本不会再启动的作业，删除它的master表
. 2.4 重新检查
1. 问题现象
Export: Release 10.2.0.5.0 - 64bit Production on Wednesday, 08 March, 2017 10:10:16
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.CSBLOG_JOB"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 866
ORA-00955: name is already used by an existing object
2. 问题处理
参考：
如何清除 DBA_DATAPUMP_JOBS 视图中的异常数据泵作业? (文档 ID 1626201.1)
2.1 查找在数据库中有哪些数据泵作业
set lines 200 pages 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE state
COL job_mode LIKE state
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;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
SYS CSBLOG_JOB EXPORT SCHEMA NOT RUNNING 0
确保在dba_datapump_jobs中列出的作业不是活动的数据泵作业，状态应该是'NOT RUNNING'. 且确认是非暂停的作业。
2.2 查找相关的master表
col "OWNER.OBJECT" for a30
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 252249 TABLE SYS.CSBLOG_JOB
2.3 对于过去被终止的和根本不会再启动的作业，删除它的master表
SQL> drop table SYS.CSBLOG_JOB purge;
Table dropped.
2.4 重新检查
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;
no rows selected
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;
no rows selected
