Kill oracle data pump export/import jobs

Background:

Generally we use ctrl+c to quit any task in windows command prompt or ctrl+z in Unix flavors. For oracle data pump exports/imports this is not the case when you type any of them, ctrl+c or ctrl+z actually pause the job and closing the command or shell window doesn’t quit the oracle data pump job rather it is running at background. so how to quit the background export or import data pump job?

Workaround:

  1. First we need to identify the export/import job we need to kill
  2. Use the KILL_JOB command in export/import prompt to kill the specified job.

Operation:

  1. Query the DBA_DATAPUMP_JOBS will let us know information about the export/import dumps jobs in the database. Select the job you want to kill from the running job/s.
    SELECT job_name, operation, state FROM DBA_DATAPUMP_JOBS;
  2. Open the command/shell prompt and login to database data pump export/import session and ATTACH your current session to the specified job needs to kill. Suppose the job name is SYS_EXPORT_SCHEMA_01
EXPDP SYSTEM/password@Oracleinstance ATTACH=SYS_EXPORT_SCHEMA_01

Now type the KILL_JOB, will prompt you whether you are sure to kill the job, type Yes and then Enter.

Database will kill the job, after sometime re-execute the above query to see the job actually killed.

A typical session for example:
C:\Users\Administrator>expdp system/password attach=SYS_EXPORT_SCHEMA_04

Export: Release 11.2.0.2.0 - Production on Sun Jan 28 13:03:02 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Pro
duction

Job: SYS_EXPORT_SCHEMA_04
 Owner: SYSTEM
 Operation: EXPORT
 Creator Privs: TRUE
 GUID: 4C73C8EEE4F54CEBA65438BB8051F92C
 Start Time: Tuesday, 23 January, 2018 11:35:06
 Mode: SCHEMA
 Instance: xe
 Max Parallelism: 1
 EXPORT Job Parameters:
 Parameter Name Parameter Value:
 CLIENT_COMMAND system/******** network_link=testmain schemas=test_dev directory=test_exp_dir dumpfile=test_dev_2018_23_01_05.dmp logfile=ftdl_dev_2018_23_01_05.log
 State: EXECUTING
 Bytes Processed: 125,692,199,304
 Percent Done: 65
 Current Parallelism: 1
 Job Error Count: 0
 Dump File: D:\TEST\TEST_DEV_2018_23_01_05.DMP
 bytes written: 125,710,565,376

Worker 1 Status:
 Process Name: DW03
 State: EXECUTING
 Object Schema: TEST_DEV
 Object Name: CORE_RETED_OUTGOING
 Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
 Completed Objects: 2,044
 Total Objects: 5,876
 Worker Parallelism: 1

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

C:\Users\Administrator>

Note: If you didn’t exit from the command/shell session after executed the export/import dump jobs then you need not to ATTACH the job as you are in the job session, simply type ctrl+c to pause the job will release the cursor to Export> or Import>; simply type the KILL_JOB and type Yes when prompted.

Leave a Reply

Your email address will not be published. Required fields are marked *