How to Kill Oracle Datapump Export Job

Hi,

When you start export or import in Oracle database with expdp and impdp command, you may want to kill or stop these jobs.

 

Let’s say You want to export full database with expdp and you used following script. Don’t forget that Nohup will be run following expdp command in background of operating system.

 

[oracle@MehmetSalih ~]$ nohup expdp \"/ as sysdba\" full=y directory=PUMP dumpfile=FULL_EXPORT22032019.dmp logfile=FULL_EXPORT2203.log &
[1] 9310
[oracle@MehmetSalih ~]$ nohup: ignoring input and appending output to `nohup.out'

[oracle@MehmetSalih ~]$

Export have started with nohup and we check export process from database.

 

[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 21 10:01:05 2019

Copyright (c) 1982, 2014, Oracle. 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

SQL> select JOB_NAME,state from dba_datapump_jobs;

JOB_NAME STATE
------------------------------ ------------------------------
SYS_EXPORT_FULL_02 EXECUTING
SYS_EXPORT_SCHEMA_02 NOT RUNNING
SYS_EXPORT_SCHEMA_01 NOT RUNNING
SYS_EXPORT_FULL_01 NOT RUNNING
SYS_EXPORT_SCHEMA_03 NOT RUNNING

SQL>
SQL> exit
Disconnected from Oracle Database 12c Release 12.1.0.2.0 - 64bit Production
[oracle@MehmetSalih ~]$

 

 

 

You can see that export process is run with SYS_EXPORT_FULL_02 EXECUTING name like above.

We can attach or connect to this process like following

[oracle@MehmetSalih ~]$
[oracle@MehmetSalih ~]$ expdp \"/ as sysdba\" attach=SYS_EXPORT_FULL_02

Export: Release 12.1.0.2.0 - Production on Fri Mar 22 12:17:08 2019

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

Connected to: Oracle Database 12c Release 12.1.0.2.0 - 64bit Production

Job: SYS_EXPORT_FULL_02
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 84AC4FBE3E0B2469E0530A20A8C0E1E2
Start Time: Friday, 22 March, 2019 12:16:37
Mode: FULL
Instance: DEVECI12C
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "/******** AS SYSDBA" full=y directory=PUMP dumpfile=FULL_EXPORT22032019.dmp logfile=FULL_EXPORT2203.log
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /backup/export/FULL_EXPORT22032019.dmp
bytes written: 4,096

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Name: +*
Object Type: DATABASE_EXPORT/TRUSTED_DB_LINK
Completed Objects: 1
Total Objects: 1
Worker Parallelism: 1

Export>

 

 

You can check status of export process like following. We can see the status of export job in this way especially

 

Export> status

Job: SYS_EXPORT_FULL_02
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /backup/export/FULL_EXPORT22032019.dmp
bytes written: 4,096

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: SYSMAN
Object Type: DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed Objects: 3
Total Objects: 3
Worker Parallelism: 1

Export>

 

If you want to kill this export job, you can kill it with kill_job command like following.

 

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

 

You can see that this export job is killed.

[oracle@MehmetSalih ~]$
[1]+ Done nohup expdp \"/ as sysdba\" full=y directory=PUMP dumpfile=FULL_EXPORT22032019.dmp logfile=FULL_EXPORT2203.log
[oracle@MehmetSalih ~]$

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.