Thursday, July 16, 2009

Oracle: How to kill data pump jobs

When you import or export using data pump impdp or expdp tools, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too

Following sql will give you the list of data pump jobs

select * from dba_datapump_jobs

If you want to kill your impdp or expdp

1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y


If by mistake, you closed the window and your import/export job is still running,

1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job
3) Once you are attached to job, Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y

And your job is killed, it will no longer show in dba_datapump_jobs

16 comments:

  1. very helpful, simple, and to the point. Thank you.

    ReplyDelete
  2. great post. thanks . i needed this process to kill the job. i couldn't figure the job name out.

    Franklin/Jason
    FranklinFaces.com

    ReplyDelete
  3. Thanks a lot, it work so well. I really appreciate,
    since I use regular kill the PID ,
    and then stuck with this big datapump job.

    D.L.

    ReplyDelete
  4. Didn't work for me... had closed window, when trying to get in w/new window, get the following:
    ORA-39001: invalid argument value
    ORA-39000: bad dump file specification
    ORA-31640: unable to open dump file 'xxxxx'
    ORA-27037: unable to obtain file status
    HP-UX Error: 2: No such file or directory
    Additional information: 3

    ReplyDelete
  5. Excellent Post Man.. Saved my Day... I owe you a Party... Cheers!!!!

    Ashish

    ReplyDelete
  6. a very useful tip, thanks a lot

    ReplyDelete
  7. Gracias, thanks, very useful

    ReplyDelete
  8. how do we kill all the jobs in state = "NOT RUNNING"
    i have around 100 jobs in that state, how to clear them ?

    ReplyDelete
    Replies
    1. for the jobs with state = "NOT RUNNING", just drop the master job table in JOB_NAME

      Example: drop table SYS_EXPORT_SCHEMA_01 purge;

      Delete
    2. Thanks ,
      it's simple and to the point .

      Delete
  9. Thanks, good one.

    ReplyDelete
  10. Thanks, its really good. Short and simple.

    ReplyDelete
  11. Good Article. Thanks.

    ReplyDelete
  12. Thanks, very helpfull!

    ReplyDelete