KILL UNWANTED DATA PUMP JOBS

KILL UNWANTED DATA PUMP JOBS


1. First we need to identify which jobs are in NOT RUNNING status. For this, we need to use below query (basically we are getting this info from dba_datapump_jobs)

SET lines 200

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
ORDER BY 1,2;

The above query will give the datapump jobs information and it will look like below

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
———- ——————- ——— ——— ———– ——–
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

In the above output, you can see state is showing as NOT RUNNING and those jobs need to be removed.

Note: Please note that jobs state will be showing as NOT RUNNING even if a user wantedly stopped it. So before taking any action, consult the user and get confirmed



2. we need to now identify the master tables which are created for these jobs. It can be done as follows

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



3. we need to now drop these master tables in order to cleanup the jobs

SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;
SQL> DROP TABLE SCOTT.SYS_EXPORT_TABLE_02 ;
SQL> DROP TABLE SCOTT.EXPDP_20051121;