KILL DATAPUMP JOB ORACLE

You have a datapump job which is running for long time and you need to kill the job due to upcoming business hour

[oracle@exdbadm01 ~]$ expdp directory=EDP dumpfile=testexdbx.dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=direct_path cluster=Y

Export: Release 11.2.0.4.0 - Production on Tue Jun 15 22:39:35 2021

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=EDP dumpfile=testexdbx.dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=direct_path cluster=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.933 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE

Here the job name is ‘ SYS_EXPORT_SCHEMA_01 ‘ as per the logfile. Use this job id and attach the job. Use kill_job to kill the job from datapump export cmd

[oracle@exdbadm01 expdp]$ expdp attach=SYS_EXPORT_SCHEMA_01

Export: Release 11.2.0.4.0 - Production on Wed Jun 16 00:38:48 2021

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

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

If it takes too longer, then identify the master job and kill it from OS side.First try to identify the master process and kill it. If you cant find it, then go tonext step

[oracle@exdbadm01 expdp]$ ps -ef|grep 'ora_dm'|grep -v 'grep'

Filter the process with expdp

[oracle@exdbadm01 expdp]$ ps -ef|grep 'expdp'|grep -v 'grep'
oracle    6905  4662  0 Jun15 pts/0    00:00:00 expdp               dumpfile=testexdbx.dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=direct_path cluster=Y
oracle    7049  4662  0 Jun15 pts/0    00:00:00 expdp               dumpfile=testexdbx.dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=direct_path cluster=Y

use KILL -9 ‘PID’ to kill the process with abrupt kill

[oracle@exdbadm01 expdp]$ kill -9 6905
[oracle@exdbadm01 expdp]$ kill -9 7049
-bash: kill: (7049) - No such process
[oracle@exdbadm01 expdp]$

The error message looks like this after job kill

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_EXPORT_SCHEMA_01" stopped due to fatal error at Wed Jun 16 00:39:03 2021 elapsed 0 01:59:17

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s