How to delete or remove non executing data pump jobs in oracle

 step 1- Identify which jobs are not in running state

SET lines 200

SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions

FROM dba_datapump_jobs

ORDER BY 1,2;

output:

OWNER_NAME       JOB_NAME       OPERATION

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

JOB_MODE STATE ATTACHED_SESSIONS

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

MANJIT       SYS_EXPORT_FULL_01       EXPORT

FULL NOT RUNNING 0

MANJIT       SYS_EXPORT_FULL_02       EXPORT

FULL NOT RUNNING 0

MANJIT       SYS_EXPORT_FULL_03       EXPORT

FULL NOT RUNNING 0

OWNER_NAME       JOB_NAME       OPERATION

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

JOB_MODE STATE ATTACHED_SESSIONS

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

MANJIT       SYS_EXPORT_FULL_04       EXPORT

FULL NOT RUNNING 0

Step 2- Identify the master tables which are created for these jobs

select 

   o.status, 

   o.object_id, 

   o.object_type, 

   o.owner||'.'||object_name "OWNER.OBJECT" 

from 

   dba_objects o, 

   dba_datapump_jobs j 

where 

   o.owner=j.owner_name 

and 

   o.object_name=j.job_name 

and 

   j.job_name not like 'BIN$%' 

order by 4, 2; 

output:

STATUS OBJECT_ID OBJECT_TYPE       OWNER.OBJECT

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

VALID     123893 TABLE       MANJIT.SYS_EXPORT_FULL_01

VALID     123993 TABLE       MANJIT.SYS_EXPORT_FULL_02

VALID     124093 TABLE       MANJIT.SYS_EXPORT_FULL_03

VALID     124193 TABLE       MANJIT.SYS_EXPORT_FULL_04

Step -3 Drop these master tables

SQL> drop table        MANJIT.SYS_EXPORT_FULL_03;

Table dropped.

SQL> drop table        MANJIT.SYS_EXPORT_FULL_02;

Table dropped.

SQL> drop table        MANJIT.SYS_EXPORT_FULL_01;

Table dropped.

Important points

1. Datapump jobs that are not running doesn’t have any impact on currently executing ones.

2. When any datapump job (either export or import) is initiated, master and worker processes will be created.

3. When we terminate export datapump job, master and worker processes will get killed and it doesn’t lead to data courrption.

4. But when import datapump job is terminated, complete import might not have done as processes(master & worker)  will be killed.