datapump: unable to attach to job to kill_job

[davegoodine]

Hi,

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,
dave

############################
Error:

$ 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

[davey23uk]

create an empty file? touch it - might work

[Bore]

sqlplus system/*****
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

[davegoodine]

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.
-dave

create an empty file? touch it - might work

[davegoodine]

Bore,

This worked for me. Thanks.

-dave

sqlplus system/*****
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

[d0hboy]

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'
SELECT
owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs;

-- 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;