oracle删除失效的expdp/impdp任务

1.查看当前DATAPUMP的状态

  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;

2.查看EXTERNAL TABLE

expdp和impdp中使用了外部表（使用了directory的一般都会用到外部表）

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
   WHERE owner NOT IN ('SYS')
ORDER BY 1, 2;

3.查看MASTER TABLE

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;

4.删除临时外部表（第2步的结果）

drop table MONKEY.ET$0005DEFF0002 purge;

5.删除主表（第3步结果）

drop table MONKEY.SYS_IMPORT_FULL_01;

参考：Clean the uncompleted DataPump jobs in Oracle