datapump: unable to attach to job to kill_job
I started then stopped a expdp job, then the dump file for the stopped job got deleted off the filesystem.
Now, when I try to attach to it, I receive an error (see below). Since I cannot attach to the job, I cannot KILL_JOB it, and the job just sits in there in dba_datapump_jobs.
I would like to clean this job out. Does anyone know how I might do this?
Thank you in advance for any assistance,
$ expdp system/***** attach=BAD_JOB
Export: Release 10.2.0.1.0 - Production on Thursday, 01 March, 2007 18:28:18
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u02/exports/datapump_export_dir/dump-file-name.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
create an empty file? touch it - might work
a good idea, but..
I tried this before posting, and it did not work. expdp detected that the dump file was empty and then exited.
creating a good dump file and renaming doesn't work either, since expdp reads the job name in the dmp file and if it doesn't match, it exits.
Thanks for the suggestion though.
Originally Posted by davey23uk
SQL> drop table BAD_JOB
when DP starts export, it creates so called master table in the user you are connected to with the DP and the table_name matches the job name. While the master table is present, the job is considered not finished
I hope that helps
This worked for me. Thanks.
Originally Posted by Bore
Hey guys, I thought I would update this thread since it came up as one of the top search hits when I tried "expdp kill datapump".
The above answer was correct, but to be explicit, it's referenced from Support.Oracle's
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? [ID 336014.1] article.
Check for datapump jobs in sqlplus:
TTITLE 'Currently Active DataPump Operations'
COL owner_name FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'JobName'
COL operation FORMAT A12 HEADING 'Operation'
COL job_mode FORMAT A12 HEADING 'JobMode'
COL state FORMAT A12 HEADING 'State'
COL degree FORMAT 9999 HEADING 'Degr|(Num|Wrkers)'
COL attached_sessions FORMAT 9999 HEADING 'Sess'
-- make sure job is in NOT RUNNING status.
Owner JobName Operation JobMode State Degr Sess
------ -------------------- ------------ ------------ ------------ ----- -----
SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 2 0
-- delete the master table:
drop job sys.sys_export_full_01;
Click Here to Expand Forum to Full Width