删除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导出jobxxx SYS_EXPORT_SCHEMA_02 EXPORT TABLE NOT RUNNING 0 --正常暂停中的schema导出job， 对于正常暂停的job可选择性清理xxx SYS_EXPORT_TABLE_01 EXPORT TABLE NOT RUNNING 0 -- 异常停止的table导出job，因此需要删除该jobSYSTEM 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_01VALID 68761 TABLE xxx.SYS_EXPDP_SCHEMA_02VALID 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; --删除后再次查看确认是否仍有输出
