Oracle DBA Tips and Techniques

By Arjun Raja

DATAPUMP – KILL JOBS ONLY USING THE DATAPUMP TOOL.

Posted by Arjun Raja on April 24, 2012

Killing an expdp or impdp process from the oslevel will not stop the job – so expdp or impdp jobs must be killed from the database level.

Example ?

Start an export job…..

Check the dba_datapump_jobs view…

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                               OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

SQL> exit

Kill the job from the oslevel …

cmi1d:/u03/oradata/export/cmi1d> ps -ef |grep expdp

oracle 26214652 18350150   0 13:27:38  pts/2  0:00 expdp / DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
cmi1d:/u03/oradata/export/cmi1d> kill -9 26214652
cmi1d:/u03/oradata/export/cmi1d>

Now check database again – a few minutes after the oslevel kill.

SQL> select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                                      OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_10             OPS$ORACLE                     EXECUTING

View still shows the job is executing.

If you look at the export dump area – it is clear the job is still executing in the background.

-rw-r—–    1 oracle   oinstall  877879296 Jun 17  13:43 expdp_full_cmi1d_170611_132738_02.dmp
-rw-r—–    1 oracle   oinstall 1382027264 Jun 17 13:43 expdp_full_cmi1d_170611_132738_01.dmp

Therefore it is not a good idea to kill a datapump job only from the oslevel – the job will not stop.

This is the way to do it.

First attach to the job via expdp….

The job name is SYS_EXPORT_FULL_10  started by OPS$ORACLE – in the view above.

To attach to a job, you must connect as the owner of the job….therefore I am using the ” / ” for connection as ops$oracle

expdp / attach=SYS_EXPORT_FULL_10

Export: Release 11.2.0.2.0 – Production on Fri Jun 17 13:45:10 2011

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

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

Job: SYS_EXPORT_FULL_10
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: A5E2A4EB79A500E4E0430A35C8EC00E4
Start Time: Friday, 17 June, 2011 13:27:42
Mode: FULL
Instance: cmi1d
Max Parallelism: 2
EXPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        /******** DUMPFILE=expdp_full_cmi1d_170611_132738_%U.dmp LOGFILE=expdp_full_cmi1d_170611_132738.log PARFILE=/u01/local/dbhk/scripts/par/expdp_full.par
COMPRESSION           ALL
INCLUDE_METADATA      1
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 2
Job Error Count: 0
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_%u.dmp
size: 8,589,934,592
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_01.dmp
size: 8,589,934,592
bytes written: 4,096
Dump File: /u02/oradata/export/expdp_full_cmi1d_170611_132738_02.dmp
size: 8,589,934,592
bytes written: 15,687,680

Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_ACCT_BALANCES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 81,746,354
Worker Parallelism: 1

Worker 2 Status:
Process Name: DW01
State: EXECUTING
Object Schema: EBS_GL_ARCHIVE
Object Name: GL_JOURNAL_LINES
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 894
Completed Rows: 91,359,564
Worker Parallelism: 1

Export>

Enter KILL_JOB to  kill job..

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): yes
.
Check again –

select job_name,owner_name,state from dba_datapump_jobs;

JOB_NAME                       OWNER_NAME                     STATE
—————————— —————————— ——————————
SYS_EXPORT_FULL_01             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_05             OPS$ORACLE                     NOT RUNNING
SYS_EXPORT_FULL_03             OPS$ORACLE                     NOT RUNNING

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: