清除Oracle DBA_DATAPUMP_JOBS中异常的数据泵作业

在一次使用expdp导数据的时候出现了异常，于是直接Ctrl+c终止了，但是发现后台进程还是在进行运行，因为directory设置在/home/oracle目录，而根目录的空间眼看着就要达到100%。

情况还是比较危险的，因为一旦根目录100%，该服务器上的数据库可能会hang住，影响正常业务的使用。

一般情况下，如果要终止当前正在运行的expdp或者impdp进程，在Ctrl+c之后进入交互模式，输入stop_job，然后输入yes即可，如下所示：

Export> stop_job 
Are you sure you wish to stop this job ([yes]/no): yes      

但是此次的情况是，直接将dumpfile删除后，因为句柄并没有释放，导致磁盘使用空间还在一直增长。

使用以下命令查看未释放的句柄进程：

$ lsof | grep t_manager.dmp 
oracle    219911  oracle   45u      REG              253,2 36478513152   22413315 /data/oracle/dump/t_manager.dmp (deleted)
查出进程号之后直接杀掉就可以      

1、查看当前数据库中有那些数据泵作业

sqlplus /nolog
CONNECT / as sysdba
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;      

2、 确保在 dba_datapump_jobs 中列出的作业不是活动的数据泵作业: 状态应该是'NOT RUNNING'。

3、同作业属主确认视图 dba_datapump_jobs 中状态为'NOT RUNNING' 的作业不是被暂停，而失败的作业。

4、使用SYSDBA在SQL*Plus中执行下面的命令来确认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
/      

这一步我是没有看到外部表。

联合DBA_OBJECTS和DBA_EXTERNAL来确认属于这个失败的作业所关联的外部表，并且删除它

SQL> drop table system.&1 purge;
Enter value for 1: ET$00654E1E0001
old 1: drop table system.&1 purge
new 1: drop table system.ET$00654E1E0001 purge      

5、查找数据泵的 Primary 表：

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;      

6、确认之后，删除异常终止的作业以及主表

drop table ADMIN.SYS_EXPORT_SCHEMA_01 ;
purge dba_recyclebin;(清理回收站)      

7、也可以使用以下语句生成批量删除语句

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$%';      

CONNECT /


SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
   h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','');
   DBMS_DATAPUMP.STOP_JOB (h1);
END;
/
      

CONNECT / as sysdba
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

-- locate Data Pump jobs: 

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;
 
 -- locate Data Pump master tables:

 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;