Cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS

Cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS

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

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) 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
---------- -------------------- ---------- ---------- ------------ -----------------
ULTIMUS       SYS_EXPORT_SCHEMA_01 EXPORT     SCHEMA     NOT RUNNING                  0
ULTIMUS       SYS_EXPORT_SCHEMA_02 EXPORT     SCHEMA     NOT RUNNING                  0


/*This view gives the SADDR that assist in determining why a Data Pump session may be
having problems. Join to the V$SESSION view for further information.*/

SELECT * FROM DBA_DATAPUMP_SESSIONS

/*This view helps determine how well a Data Pump export is doing.
Basically gives you a progress indicator through the MESSAGE column.*/

select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS


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.

SELECT 'drop table ULTIMUS.'||job_name||';'
FROM dba_datapump_jobs t;
--where t.job_name<>'SYS_EXPORT_SCHEMA_98';


drop table ULTIMUS.SYS_EXPORT_SCHEMA_98;

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
---------- -------------------- ---------- ---------- ------------ -----------------
ULTIMUS       BIN$xMNQdACzQ6yl22kj EXPORT     SCHEMA     NOT RUNNING                  0
          9U0B8A==$0
ULTIMUS       BIN$BmUy4r5MSX+ojxFk EXPORT     SCHEMA     NOT RUNNING                  0
          sw8ocg==$0


SELECT 'PURGE table ULTIMUS.'||job_name||';'
FROM dba_datapump_jobs t;
--where t.job_name<>'SYS_EXPORT_SCHEMA_98';
PURGE table ULTIMUS.SYS_EXPORT_SCHEMA_98;

SQL> PURGE TABLE ULTIMUS.SYS_EXPORT_SCHEMA_01;

SQL> PURGE TABLE ULTIMUS.SYS_EXPORT_SCHEMA_02;

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','ULTIMUS');
  DBMS_DATAPUMP.STOP_JOB (job1);
END;
/

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