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:
- First we need to identify the export/import job we need to kill
- Use the KILL_JOB command in export/import prompt to kill the specified job.
Operation:
- 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;
- 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.