Identifying data pump jobs to be /Kill/Stop/Resume

Do a select from dba_datapump_jobs in sqlplus to get the job name:

> expdp system full=y

SELECT owner_name, job_name, operation, job_mode, state

FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE

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

SYSTEM     SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING

Or when you use the JOB_NAME parameter when datapumping, you already identified the job with a name. You don’t need to look up afterwards…

expdp system full=y JOB_NAME=EXP_FULL

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE

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

SYSTEM     EXP_FULL             EXPORT     FULL       EXECUTING

Killing or stopping a running datapump job:

The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!

When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB orSTOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…

Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

[Ctrl-c]

Export> KILL_JOB

..or..

Export> STOP_JOB=IMMEDIATE

Are you sure you wish to stop this job ([yes]/no): yes

Resuming a stopped job:

Identify your job with SQL or you already knew it because you used ‘JOB_NAME=‘ ;)

SELECT owner_name, job_name, operation, job_mode, state

FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      

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

SYSTEM     EXP_FULL             EXPORT     FULL       NOT RUNNING

Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:

> expdp system ATTACH=EXP_FULL

Job: EXP_FULL

 Owner: SYSTEM

 Operation: EXPORT

 Creator Privs: TRUE

 GUID: A5441357B472DFEEE040007F0100692A

 Start Time: Thursday, 08 June, 2011 20:23:39

 Mode: FULL

 Instance: db1

 Max Parallelism: 1

 EXPORT Job Parameters:

 Parameter Name      Parameter Value:

 CLIENT_COMMAND        system/******** full=y JOB_NAME=EXP_FULL

 State: IDLING

 Bytes Processed: 0

 Current Parallelism: 1

 Job Error Count: 0

 Dump File: /u01/app/oracle/admin/db1/dpdump/expdat.dmp

 bytes written: 520,192

Worker 1 Status:

 Process Name: DW00

 State: UNDEFINED

(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).

Export> START_JOB[=SKIP_CURRENT]

Export> CONTINUE_CLIENT

Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26

Restarting "SYSTEM"."EXP_FULL":  system/******** full=y JOB_NAME=EXP_FULL

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

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.