清除dba_datapump_jobs中的孤兒資料泵job

1、Determine in SQL*Plus which Data Pump jobs exist in the database:
sqlplus / 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

SQL> 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_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
LIS        SYS_EXPORT_SCHEMA_01 EXPORT       SCHEMA       NOT RUNNING                  0
LIS        SYS_EXPORT_SCHEMA_02 EXPORT       SCHEMA       NOT RUNNING                  0

2、Ensure that the listed jobs in dba_datapump_jobs are not export/import Data Pump jobs that are active: status should be 'NOT RUNNING'

3、Check with the job owner that the job with status 'NOT RUNNING' in dba_datapump_jobs is not an export/import Data Pump job that has been temporary stopped, but is actually a job that failed

4、Determine in SQL*Plus the related master tables:
SQL> col OWNER.OBJECT for a30
SQL> 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        94486 TABLE               LIS.SYS_EXPORT_SCHEMA_01
VALID       151661 TABLE               LIS.SYS_EXPORT_SCHEMA_02

5、For jobs that were stopped in the past and won't be restarted anymore, delete the master table. E.g.:
drop table LIS.SYS_EXPORT_SCHEMA_01;
drop table LIS.SYS_EXPORT_SCHEMA_02;
-- For systems with recycle bin additionally run:
purge dba_recyclebin;

6、Re-run the query on dba_datapump_jobs and dba_objects (step 1 and 4). If there are still jobs listed in dba_datapump_jobs, and these jobs do not have a master table anymore, cleanup the job while connected as the job owner. E.g.:

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

Note that after the call to the STOP_JOB procedure, it may take some time for the job to be removed. Query the view user_datapump_jobs to check whether the job has been removed:

SELECT * FROM user_datapump_jobs;

7、Confirm that the job has been removed:
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

SQL> 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;

未選定行

SQL> col OWNER.OBJECT for a30
SQL> 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;

未選定行