The expdp and impdp utilities are command-line driven, but when starting them from the OS-prompt, one does not notice it. When you run impdp or expdp and use ‘ctrl-c’ and you want to kill, cancel, start or resume a job, you will end 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, this is useful when you hit errors when a master table already exists (see ATTACH):
> impdp system (...) ORA-31626: job does not exist ORA-31633: unable to create master table "SYSTEM.<TABLE_NAME>" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.KUPV$FT", line 1163 ORA-00955: name is already used by an existing object ORA-06512: at "SYS.KUPV$FT", line 1056 ORA-06512: at "SYS.KUPV$FT", line 1044 SQL> 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, 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 ‘work item’ datapump was working on. To show progress again, type CONTINUE_CLIENT.
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
Done…
Happy pumping!
Thanks!!!
Very Usefull…. Thanks
merci beaucoup pour l’astuce !
Good Article
Thanks for sharing the document.
MOre interesting if you cut the terminal it’s also working…. just check log file
happy dumpingg…………
What if it keeps on running ‘good jobs’ in the background? You need to attach and cancel…
Excellent job…………
Very nice,usefull….
Excelente aporte, muchas gracias
thnx
Thanks a lot, you saved my day ! :-)
Many Thanks, my server crashed after more than 24hoours of export !!!
I can resume my export thanks to your procedure
Regards
You’re welcome!
thanks
Thanks
Very nicely written,Thanks for the help.
We have had issues with expdp processes. The dba_datapump_jobs table has the following:
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
———- ——————- ——— ——– ———– —————–
SYSTEM SYS_EXPORT_TABLE_01 EXPORT TABLE EXECUTING 0
SYSTEM SYS_EXPORT_TABLE_11 EXPORT TABLE EXECUTING 0
SYSTEM SYS_EXPORT_TABLE_12 EXPORT TABLE EXECUTING 0
SYSTEM SYS_EXPORT_TABLE_13 EXPORT TABLE EXECUTING 0
SYSTEM SYS_EXPORT_TABLE_14 EXPORT TABLE EXECUTING 0
SYSTEM SYS_EXPORT_TABLE_15 EXPORT TABLE EXECUTING 0
SYSTEM SYS_EXPORT_TABLE_16 EXPORT TABLE EXECUTING 0
But the associated tables and no longer exist and there is nothing to attach to.
You might want to check Oracle support note 336014.1 about Orphaned Data Pump jobs.
It’s a pretty well explained document with 8 steps to get rid of them.
It basically comes to join dba_datapump_jobs with dba_objects and find the internal jobs tables and drop those.
Thank you Ian, great info.
But what happens if you have job running and bounce the database, this job will be resumable?
manual, or automatic, or will be lost?
i test it an can be restarted manually.
Well, the job stopped working and everything not commited will be rolled back. The job definition is still there so you can manually start it again or it will run on next moment of scheduling.
You SAVED my JOB!!!!!!! , I had done a expdp to default DUMP_DIR and i didnt check whether it was root dir or not… i saw my root increasing even though i ctrl+c ‘ed…… GOD BLESS YOUR SOUL.!!!!
You’re welcome :)