5

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

2 Answers 2

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.

0
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;
1
  • maybe there are some sessions that should be killed, too,
    – miracle173
    Feb 6, 2018 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.