Mani's Blog

May 22, 2012

How to kill interrupted Oracle impdp/expdp job.

Filed under: Database,Oracle — mani @ 6:18 pm
Tags: , , ,

How to kill interrupted impdp/expdp job.

I have kicked in impdp then pressed ctrl+c and exit due to some errors.  But the job will never die, it needs to be cleared properly in order to proceed further. Here is the step I followed to kill the job.
Select owner and job_name:

SQL> select *from dba_datapump_jobs;

OWNER_NAME               JOB_NAME
—————————— ——————————
OPERATION
——————————————————————————–
JOB_MODE
——————————————————————————–
STATE                   DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
SYSTEM                   SYS_IMPORT_FULL_01
IMPORT
FULL
EXECUTING                1          0            2

Killing the Job:

[oracle@xnodb911 ~]$ impdp system attach=system.SYS_IMPORT_FULL_01

Import: Release 11.1.0.7.0 – 64bit Production on Tuesday, 22 May, 2012 18:11:25

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

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

Job: SYS_IMPORT_FULL_01
Owner: SYSTEM
Operation: IMPORT
Creator Privs: TRUE
GUID: C0A4D32CF738BCCBE040100A54626673
Start Time: Tuesday, 22 May, 2012 18:02:44
Mode: FULL
Instance: PRETZEL
Max Parallelism: 1
EXPORT Job Parameters:
CLIENT_COMMAND        system/******** dumpfile=xnodb023.dmp directory=dmpdir logfile=xnodb023.log SCHEMAS=XN_SEARCH,SCRAWLER,ORAMONKEY,XN_EXPIMP,S_2811027_ACLU23,S_2797846_PRETZE,XN_CORE,XN_DBA version=10.2
IMPORT Job Parameters:
Parameter Name      Parameter Value:
CLIENT_COMMAND        system/******** dumpfile=xnodb023.dmp directory=dmpdir logfile=xnodb023_imp.log full=y
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /local/data/oracle/nfs/xnodb023.dmp

Worker 1 Status:
Process Name: DW01
State: EXECUTING
Object Schema: S_2797846_PRETZE
Object Name: SHAPE_MAINUSER_132
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 38
Completed Rows: 7,504,787
Completed Bytes: 8,638,320,560
Percent Done: 100
Worker Parallelism: 1

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

[oracle@xnodb911 ~]$

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: