Kill, cancel and resume or restart datapump expdp and impdp jobs


The expdp and impdp utilities are command-line driven, but when starting them from the OS-prompt, one does not notice it. When you want to kill, cancel, start or resume a job, you will and up in the datapump command prompt… now what?!
All command shown here can be used with expdp and impdp datapump.
Identifying datapump jobs
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 or STOP_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,
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,
Restarting “SYSTEM”.”EXP_FULL”: system/******** full=y JOB_NAME=EXP_FULL

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Done…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s