DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: datapump: unable to attach to job to kill_job

  1. #1
    Join Date
    Mar 2007
    Posts
    3

    datapump: unable to attach to job to kill_job

    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:

    Code:
    $ 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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    create an empty file? touch it - might work

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  4. #4
    Join Date
    Mar 2007
    Posts
    3

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

    Quote Originally Posted by davey23uk
    create an empty file? touch it - might work

  5. #5
    Join Date
    Mar 2007
    Posts
    3
    Bore,

    This worked for me. Thanks.

    -dave

    Quote Originally Posted by 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

  6. #6
    Join Date
    Apr 2007
    Posts
    1
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width