Oracle Technology Sharing!!!: How to cleanup orphaned datapump jobs from DBA_DATAPUMP

In many cases you sometimes stop data pump job or in case of an abnormal end of the Data Pump job (the orphaned job) or using undocumented parameter KEEP_MASTER=Y, the master table remain in the database.

Though this topic is related to cleanup orphaned datapump jobs. But it is good to know several things before doing cleanup jobs.

1) You can check the orphaned data pump from the state column of the view dba_datapump_jobs and DBA_DATAPUMP_JOBS is based on gv$datapump_job, obj$, com$, and user$. Orphaned Data Pump jobs do not have an impact on new Data Pump jobs. If a new Data Pump job is started, a new entry will be created, which has no relation to the old Data Pump jobs.

2) For a new data pump job without any job name it is used a system generated name. From the dba_datapump_jobs it is checked for existing data pump jobs and then obtain a unique new system generated jobname.

3) Data pump jobs are different from DBMS_JOBS and they are maintained differently. Jobs created with DBMS_JOBS use there own processes. Data Pump jobs use a master process and worker process(es).

4) If you drop the master table while doing the data pump export or data pump import operation then the scenario is discussed below.

In case of export if you drop data pump export operation then export process will abort.

In case of import if you drop data pump import operation then import process will abort while it leads an incomplete import.

If the data pump job is completed and master table exist (a common if you do export operation with KEEP_MASTER=y) then it is safe to drop the master table.

Step by step cleanup orphaned datapump jobs is discussed below.

Step 01: Check the orphaned datapump jobs.

sqlplus / as sysdba
SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12 
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ARJU       SYS_EXPORT_SCHEMA_01 EXPORT     SCHEMA     NOT RUNNING                  0
ARJU       SYS_EXPORT_SCHEMA_02 EXPORT     SCHEMA     NOT RUNNING                  0

Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING. So from the output we can say both are orphaned jobs.

Step 03: Drop the master table.
DROP TABLE ARJU.SYS_EXPORT_SCHEMA_01;
DROP TABLE ARJU.SYS_EXPORT_SCHEMA_02;

Step 04: Check for existing data pump jobs by query issued in step 01. If objects are in recyclebin bin then purge the objects from the recyclebin.

SQL> SELECT owner_name, job_name, operation, job_mode,
   state, attached_sessions
   FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE        ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
ARJU       BIN$xMNQdACzQ6yl22kj EXPORT     SCHEMA     NOT RUNNING                  0
          9U0B8A==$0
ARJU       BIN$BmUy4r5MSX+ojxFk EXPORT     SCHEMA     NOT RUNNING                  0
          sw8ocg==$0

SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_01;

Table purged.

SQL> PURGE TABLE ARJU.SYS_EXPORT_SCHEMA_02;

Table purged.

Check if there is any orphaned jobs again.
SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

no rows selected

Step 05: In this stage you did not get any orphaned jobs if the jobs have a master table. If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

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

DECLARE
  job2 NUMBER;
BEGIN
  job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','ARJU');
  DBMS_DATAPUMP.STOP_JOB (job2);
END;
/