advertisements
_____________________________________________________________________________________________________________________
Terminate Data Pump (EXPDP IMPDP) Executing Jobs exited without KILL_JOB command?
Here is the Scenario.
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
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
ORA-00942: table or view does not exist
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment