Terminate/Kill/Stop Data Pump EXPDP IMPDP Executing Jobs

Terminate Data Pump (EXPDP IMPDP) Executing Jobs exited without KILL_JOB command?

Here is the Scenario.

I have initiated an expdp job and exited without killing the job. In this scenario I cannot reinitiate the expdp with the same name again as it is in the executing status. You can use “drop table <jobname>;” to terminate such kind of jobs. See the below example.

$ expdp dumpfile=exp_table.dmp logfile=exp_table.log
tables=scott.object_list job_name=exptable directory=exp_dir

Export: Release 11.2.0.2.0 - Production on Tue Feb 7 06:59:16 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."EXPTABLE":  /******** AS SYSDBA dumpfile=exp_table.dmp logfile=exp_table.log tables=scott.object_list job_name=exptable directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.678 GB

Export> exit

Please see the dba_datapump_jobs records.

SQL> select * from dba_datapump_jobs;

OWNER_NAME                     JOB_NAME
------------------------------ ------------------------------
OPERATION
--------------------------------------------------------------------------------
JOB_MODE
--------------------------------------------------------------------------------
STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
------------------------------ ---------- ----------------- -----------------
SYS                            EXPTABLE
EXPORT
TABLE
EXECUTING                               1                 0                 2


You can terminate the job using following command.
Sqlplus / as sysdba
SQL> drop table EXPTABLE;

Table dropped.
Where exptable is the job_name which I mentioned during the export.

The expdp logfile content will be like this.

tail -f exp_table.log
0xdead5488      9068  package body SYS.KUPW$WORKER
0xdead5488      1688  package body SYS.KUPW$WORKER
0xdaae52b0         2  anonymous block
ORA-39097: Data Pump job encountered unexpected error -942
ORA-39065: unexpected master process exception in DISPATCH
ORA-00942: table or view does not exist
Job "SYS"."EXPTABLE" stopped due to fatal error at 06:59:49
ORA-39097: Data Pump job encountered unexpected error -942
ORA-39065: unexpected master process exception in KUPM$MCP.SET_LONGOPS
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google