中止僵死的expdp任务

--dba_datapump_jobs查找数据泵作业

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
---------- ------------------- --------- --------- ----------- --------
SCOTT      EXPDP_20051121      EXPORT    SCHEMA    EXECUTING          1
SCOTT      SYS_EXPORT_TABLE_01 EXPORT    TABLE     NOT RUNNING        0 
SCOTT      SYS_EXPORT_TABLE_02 EXPORT    TABLE     NOT RUNNING        0 
SYSTEM     SYS_EXPORT_FULL_01  EXPORT    FULL      NOT RUNNING        0

--查找数据泵的master表

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        85215 TABLE        SCOTT.SYS_EXPORT_TABLE_02
VALID        85162 TABLE        SYSTEM.SYS_EXPORT_FULL_01

--删除master表

DROP TABLE scott.sys_export_table_02;

--对于启用了recyclebin的系统，需要额外运行

purge dba_recyclebin;

-------------正常中止expdp作业---------------------------

--查到job_name

select * from dba_datapump_jobs;
OWNER_NAME   JOB_NAME  OPERATION  JOB_MODE   STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------------------------------------ ------------------------------ ----------
SYSTEM       SYS_EXPORT_FULL_01    EXPORT   FULL  EXECUTING                               1                 1                 3

--attach该任务

expdp system/sys attach=SYS_EXPORT_FULL_01

Export> stop_job

Export> KILL_JOB