Killing a datapump export

At many occasions you would like to kill the data pump export and there is a clean way to do this. In the example below I will demonstrate how to do export and then kill that export cleanly.

1. Start the export using expdp
2. Log in to database and see data pump jobs;
3. Attach to export session 
4. Kill the export job 
5. Check if there is any export operation still running.

Steps 1:
Start the export and below is how it was started.

[oracle@hdhillon-dt ~]$ expdp system/******** directory=EXPORT_DIR dumpfile=testexp.dmp logfile=testexp.log full=Y
Export: Release 11.2.0.1.0 - Production on Thu May 22 08:05:48 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/******** directory=EXPORT_DIR dumpfile=testexp.dmp logfile=testexp.log full=Y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 23.92 GB
Processing object type DATABASE_EXPORT/TABLESPACE

Step 2:
Log in to database and see what export operations are running.

col owner_name for a10
col job_name for a20
col OPERATION for a10
col JOB_MODE for a10
col state for a15
select * from dba_datapump_jobs;

08:07:35 SQL> select * from dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                      JOB_MODE                       STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------- ----------------- -----------------
SYSTEM                         SYS_EXPORT_FULL_01             EXPORT                         FULL                           EXECUTING                               1                 1                 3
08:08:27 SQL>

Step 3:
Attach to export process from another session

[oracle@hdhillon-dt ~]$ expdp system/******** attach=SYSTEM.SYS_EXPORT_FULL_01
Export: Release 11.2.0.1.0 - Production on Thu May 22 08:08:47 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Job: SYS_EXPORT_FULL_01
  Owner: SYSTEM                      
  Operation: EXPORT                      
  Creator Privs: TRUE                        
  GUID: F9F133159BE95D68E040A68907571827
  Start Time: Thursday, 22 May, 2014 8:05:56
  Mode: FULL                        
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=EXPORT_DIR dumpfile=testexp.dmp logfile=testexp.log full=Y
  State: EXECUTING                    
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /stage/datapump/testexp.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                    
  Object Schema: OE
  Object Name: PRODUCT_DESCRIPTIONS
  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE
  Completed Objects: 1,188
  Worker Parallelism: 1
Export>

Step 4:
Kill the export process.

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
[oracle@hdhillon-dt ~]$

Step 5:
Check if there are still any oracle datapump export running.

08:09:02 SQL> select * from dba_datapump_jobs;
no rows selected
08:09:20 SQL> 

The message on session from where I was exporting is as below:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at 08:09:06
[oracle@hdhillon-dt ~]$ 

Other commands which can be used are:

Export> help
------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.


Export>

There is another way to stop / kill running expdp job using SQL and discussed in another post here