删除DBA_DATAPUMP_JOBS视图中的异常数据泵JOB的方法具体步骤

1. 查看当前有哪些数据泵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 a50
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

---------- ------------------- --------- --------- ----------- --------

xxx SYS_EXPDP_SCHEMA_01 EXPORT SCHEMA EXECUTING 1  --正在运行的schema导出job

xxx SYS_EXPORT_SCHEMA_02 EXPORT TABLE NOT RUNNING 0 --正常暂停中的schema导出job， 对于正常暂停的job可选择性清理

xxx SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0  -- 异常停止的table导出job，因此需要删除该job

SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0  --正常暂停中的全库导出job，对于正常暂停的job可选择性清理

2. 查出DataPump的外部表

set linesize 200 trimspool on
set pagesize 2000
col owner form a30
col created form a25
col last_ddl_time form a25
col object_name form a30
col object_type form a25
select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/
select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2
/

3. 删除失败job相关联的外部表

需要结合DBA_OBJECTS和DBA_EXTERNAL来确认属于这个失败的作业所关联的外部表，然后drop掉：

SQL> drop table system.xxx  PURGE;  --加上purge这个参数

4. 找出数据泵的primary表

（1）

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 68723 TABLE xxx.SYS_EXPDP_SCHEMA_01

VALID 68761 TABLE xxx.SYS_EXPDP_SCHEMA_02

VALID 68297 TABLE SYSTEM.SYS_EXPORT_FULL_01

注：

异常的job不会有输出

（2）

select table_name, owner from dba_external_tables;

5. 删除primary表（可选）

对于被终止的和根本不会再启动的job，可以删除它们的primary表：

SELECT 'DROP TABLE '||o.owner||'."'||object_name||'" PURGE;'  --表名加了双引号，用来解决有时大小写不一致报错的问题
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$%';

根据上面的SQL输出，执行drop操作并清空回收站：

DROP TABLE XXX.SYS_EXPORT_SCHEMA_02 PURGE;  --删除正常暂停的job
purge dba_recyclebin;  --启用了recyclebin就需要运行

6. 重新运行第2步和第4步的SQL，如果 dba_datapump_jobs 里仍然有作业列出，并且这些作业根本没有出现在Primary表，需要登录对应的job用户来删除

sqlplus xxx/xxx
SET serveroutput on
SET lines 100
DECLARE
h1 NUMBER;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','XXX');
DBMS_DATAPUMP.STOP_JOB (h1);
END;
/
SELECT * FROM user_datapump_jobs;  --删除后再次查看确认是否仍有输出