【ASK_ORACLE】刪除DBA_DATAPUMP_JOBS檢視中的異常資料泵JOB的方法

刪除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;  --刪除後再次檢視確認是否仍有輸出