-- Step 01: Check the orphaned datapump jobs.
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 
ORDER BY 1;

OWNER_NAME JOB_NAME              OPERATION      JOB_MODE    STATE        ATTACHED_SESSIONS
---------- --------------------  -------------- ----------- ------------ -----------------
BACKUP     SYS_EXPORT_FULL_01    EXPORT         FULL        NOT RUNNING                  0
C##SANTOS  SYS_IMPORT_SCHEMA_01  IMPORT         SCHEMA      NOT RUNNING                  0
 
-- Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING.  
-- Step 03: Drop the master table.
DROP TABLE USER.SYS_EXPORT_SCHEMA_01;
DROP TABLE USER.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> PURGE TABLE USER.SYS_EXPORT_SCHEMA_01;
Table purged.

SQL> PURGE TABLE USER.SYS_EXPORT_SCHEMA_02;
Table purged.


-- 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','USER');
 DBMS_DATAPUMP.STOP_JOB (job1);
END;
/
DECLARE
 job2 NUMBER;
BEGIN
 job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','USER');
 DBMS_DATAPUMP.STOP_JOB (job2);
END;
/