Delete or Cleanup Orphaned Data Pump Jobs

Sometimes we need stop the datapump operation due to some reason .  To cleanup orphaned datapump jobs we perform the following steps.

1.)  Check the orphaned datapump jobs.
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

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------------

SYSTEM                         SYS_EXPORT_SCHEMA_01           EXPORT                         SCHEMA                NOT RUNNING                                    0

SYSTEM                         SYS_EXPORT_SCHEMA_03           EXPORT                         SCHEMA                NOT RUNNING                                    0

SYSTEM                         SYS_EXPORT_SCHEMA_02           EXPORT                         SCHEMA                NOT RUNNING                                    0

2.)  Check the status of  "state"  field

For orphaned jobs the state will be NOT RUNNING. So from the output we can say all the three are orphaned jobs. 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.

3.)  Drop the master table  

Since  the  above  jobs  are  stopped  or  not running  won't  be  restarted  anymore,  so  drop  the master table. The master  tables  above  are  SYS_EXPORT_SCHEMA_01,   SYS_EXPORT_SCHEMA_03,SYS_EXPORT_SCHEMA_02) .

SQL> drop table  system.SYS_EXPORT_SCHEMA_03 ; 

Table dropped.

SQL> drop table  system.SYS_EXPORT_SCHEMA_01 ; 

Table dropped.

SQL> drop table  system.SYS_EXPORT_SCHEMA_02 ; 

Table dropped.


4.) Check  for  existing  data  pump  jobs 
Now check the existing datapump job by  query  issued  in  step 1.  If  objects  are  in  recyclebin  then purge the objects from the recyclebin. 

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

No row selected

SQL> purge table system.SYS_EXPORT_SCHEMA_01;
Table purged.
SQL> purge table system.SYS_EXPORT_SCHEMA_02;
Table purged

SQL> purge table system.SYS_EXPORT_SCHEMA_03;

Table purged

SQL>select owner_name, job_name, operation, job_mode, state, attached_sessions from dba_datapump_jobs; 

 no rows selected