Terminate Datapump Job
A data pump job runs on the server. It cannot be terminated by killing the client session which started it. Follow these steps to terminate a data pump export job.
1. Open a separate terminal window and start SQL*Plus. Find the name of the running job.
SQL> select owner_name, job_name from dba_datapump_jobs;
2. Start new datapump session and attach to job name retrieved in previous step.
$ expdp system/mypwd attach=<job_name>
3. Terminate the job
Export> kill_job
Demo
The examples below use a Linux guest on Oracle VBox. The first one runs a data pump export in the background, and attaches to it using the job name. The second one runs the export in the foreground and then terminates the client process using CTL-C; the server process is then killed from data pump.
Example 1
Start a DP export job in the background. Query the dictionary to find it’s name. Attach to it and issue the kill job command.
— Start data pump export in the background.
$ nohup expdp system/blogtest directory=dpump full=y dumpfile=full.dmp logfile=full.log &
— Log into SQLPLUS and find job name.
$ sqlplus system/blogtest SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 10:30:04 201 SQL> select job_name,state from user_datapump_jobs; JOB_NAME STATE --------------------- ------------------------------ SYS_EXPORT_FULL_01 EXECUTING SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
— Attach to the data pump job and kill it.
$ expdp system/blogtest attach=SYS_EXPORT_FULL_01 Export: Release 12.1.0.2.0 - Production on Sun Jun 3 10:30:48 2018 Job: SYS_EXPORT_FULL_01 Owner: SYSTEM Operation: EXPORT Creator Privs: TRUE GUID: 6DBEA56196A71FBCE0536338A8C0DC89 Start Time: Sunday, 03 June, 2018 10:29:36 Mode: FULL Instance: ocp12c Max Parallelism: 1 Timezone: +00:00 Timezone version: 18 Endianness: LITTLE NLS character set: AL32UTF8 NLS NCHAR character set: AL16UTF16 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /u01/fra/db1/dp/full.dmp bytes written: 4,096 Worker 1 Status: Instance ID: 1 Instance name: ocp12c Host name: d12c1.localdomain Process Name: DW00 State: EXECUTING Object Schema: SYS Object Name: AUD$Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 6DBEA56196A71FBCE0536338A8C0DC89
Start Time: Sunday, 03 June, 2018 10:29:36
Mode: FULL
Instance: ocp12c
Max Parallelism: 1
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u01/fra/db1/dp/full.dmp
bytes written: 4,096 Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Completed Objects: 60 Worker Parallelism: 1 Export> kill_job Are you sure you wish to stop this job ([yes]/no): yes
— Verify job has been terminated.
$ sqlplus system/blogtest SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 10:31:07 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Jun 03 2018 10:30:48 -04:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select job_name,state from user_datapump_jobs; no rows selected
Example 2
Start a DP export job, and press CTL-C. Issue kill job command to terminate it.
— Start data pump export job in the foreground and press CTL-C to terminate the client session.
$ expdp system/blogtest directory=dpump full=y dumpfile=full.dmp logfile=full.log
Export: Release 12.1.0.2.0 – Production on Sun Jun 3 10:47:39 2018
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_FULL_01″: system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.578 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
^C
-- Pressing CTL-C while the job is running brings up inter-active command mode. Type HELP to see the options available. 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> status Job: SYS_EXPORT_FULL_01 Operation: EXPORT Mode: FULL State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /u01/fra/db1/dp/full.dmp bytes written: 4,096 Worker 1 Status: Instance ID: 1 Instance name: ocp12c Host name: d12c1.localdomain Process Name: DW00 State: EXECUTING Object Name: +* Object Type: DATABASE_EXPORT/TRUSTED_DB_LINK Completed Objects: 1 Total Objects: 1 Worker Parallelism: 1
— issue the kill job command
Export> kill_job Are you sure you wish to stop this job ([yes]/no): yes
— verify job is not running anymore.
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Jun 3 10:48:34 2018 SQL> select owner_name, job_name,state from dba_datapump_jobs; no rows selected
— check the export job log.
$ cd /u01/fra/db1/dp $ cat full.log Export: Release 12.1.0.2.0 - Production on Sun Jun 3 10:47:39 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** directory=dpump full=y dumpfile=full.dmp logfile=full.log Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 5.578 MB Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY ;;; Export> help ;;; Export> status Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM ;;; Export> kill_job Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Sun Jun 3 10:48:19 2018 elapsed 0 00:00:38 $