Monday 29 July 2013

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.

No comments:

Post a Comment