How do I kill Oracle DataPump expdp job stuck in DEFINING state?

Question

My job failed because dump file already existed. Now it's stuck in DEFINING state.

How do I stop/kill it?

declare
h1 number;
BEGIN
h1 := DBMS_DATAPUMP.ATTACH('EXPORT_TABLE_2017_1205_1402',user);
--DBMS_DATAPUMP.STOP_JOB (h1,1,0);
DBMS_DATAPUMP.stop_job (h1, 1);
DBMS_DATAPUMP.detach (h1);
END;
/

  2    3    4    5    6    7    8    9  declare
*
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3873
ORA-06512: at line 4

Using expdp getting segmentation fault:

 expdp stgdata@ORADB1S attach=EXPORT_TABLE_2017_1205_1402

Export: Release 11.2.0.3.0 - Production on Tue Dec 5 14:56:11 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: EXPORT_TABLE_2017_1205_1402
  Owner: STGDATA
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 5F9D23C27A291F20E05328C414AC3548
  Start Time: Tuesday, 05 December, 2017 14:02:38
  Mode: TABLE
  Instance: ORADB1S
  Max Parallelism: 0
  EXPORT Job Parameters:
Segmentation fault

Any idea how do kill em?

select sid, serial# from v$session where action like 'EXPORT_TABLE%';

520 1161    wait for unread message on broadcast channel
587 635     wait for unread message on broadcast channel

Trying to restart same job:

ERROR at line 1:
ORA-31634: job already exists
ORA-06512: at "STGDATA.DELETE_EXPORT_TABLE", line 59
ORA-06512: at line 1

Answer 1

I have encountered this before, if I remember correctly : it should disappear next time you stop then start (aka bounce) the database.

Here is the link I used.

Answer 2

expdp stgdata@ORADB1S attach=EXPORT_TABLE_2017_1205_1402
EXPORT> Kill_job

Have you tried dropping the export table?

DROP TABLE SYS.EXPORT_TABLE_2017_1205_1402;