Data Pump

How to Interrupt and Kill a Data Pump Job

Sometimes, you are unable to stop the import or export job on the spot, and no one knows whether it’s hanging or not. In such case, you can initiate another console and attache the import or export job to take over the control like this:

[oracle@test ~]$ impdp system/password attach=SYS_IMPORT_SCHEMA_01

Import: Release 11.2.0.4.0 - Production on X

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Job: SYS_IMPORT_SCHEMA_01
  Owner: SYSTEM
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: 42E4C5579DFF06BAE053CA2B400A814B
  Start Time: X
  Mode: SCHEMA
  Remote link: orcl_source_link
  Instance: orcl
  Max Parallelism: 4
  EXPORT Job Parameters:
  IMPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** content=data_only table_exists_action=truncate parfile=schemas.par network_link=orcl_source_link status=300 parallel=4 logfile=imp_schemas.log
     INCLUDE_METADATA      0
     TABLE_EXISTS_ACTION   TRUNCATE
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 4
  Job Error Count: 0

Worker 1 Status:
  Process Name: DW00
  State: WORK WAITING

Worker 2 Status:
  Process Name: DW01
  State: EXECUTING
  Object Schema: NHRI
  Object Name: OVERTIME
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 46
  Total Objects: 4,403
  Worker Parallelism: 1

Worker 3 Status:
  Process Name: DW02
  State: WORK WAITING

Worker 4 Status:
  Process Name: DW03
  State: WORK WAITING

Now you’re in the interactive mode of data pump and the current job is SYS_IMPORT_SCHEMA_01. If you’d like to terminate the current job, you can send kill_job to the operation.

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

More interactive commands can be found in the official documentation:
Commands Available in Import’s Interactive-Command Mode

Leave a Reply

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