How To Delete or Cleanup Orphaned Data Pump Jobs

Sometimes we stopover the datapump computation due to few represent and speak it afterwards (as inform in preceding situation) . To earnings unparented datapump jobs we accomplish the succeeding steps. Step 01: Check the orphaned datapump jobs. SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions,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 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. (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. 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; no rows 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 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 we 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; /