attaching to the job in DEFINING state

From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
Date: Thu, 19 Nov 2015 12:47:51 +0100
Message-ID: <564DB6E7.9020008_at_nordea.com>

hi,

currently I hit the following (somewhat academic) problem - wonder if anybody would point me in the right direction. Here is the background:

I try to create a procedure to run a repeatable data pump job. I run into the following scenario:
- run export job once - it works properly dumping to a file

• run export job twice - it throws the exception about the existing file, which is expected
• run export job 3rd time - now I 've got a message about ORA-31634 (the job already exists) and ORA-31637

All is clear and understandable - the code throws exception, it is caught, the message put to output, job detached. The detachment is done as I 've found an excerpt about jobs being detached should cease to exist, which is basically what I want to achieve. However the job is still there - in this case in the DEFINING state. I am able to attach to it and kill it, but not from the code - only by using impdp/expdp.

I tried simply
DECLARE
 job1 NUMBER;
BEGIN
 job1 :=
DBMS_DATAPUMP.ATTACH(job_name=>'DUMP_SCHEMA_EXAMPLE',job_owner=>'MX_ADM'); dbms_output.put_line('job1:'||job1);
END;
/

but get ORA-31626 (the job does not exist), which is the perfect contradiction to the 3rd run outcome

Using expdp works just fine

---

expdp mx_adm/mx_adm01 attach=DUMP_SCHEMA_EXAMPLE

Export: Release 11.2.0.2.0 - Production on Thu Nov 19 09:55:57 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
- 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: DUMP_SCHEMA_EXAMPLE

  Owner: MX_ADM                        
  Operation: EXPORT                        
  Creator Privs: TRUE                          

  GUID: 24E11530E0EC65BFE05400144FF946DB   Start Time: Thursday, 19 November, 2015 9:04:50   Mode: SCHEMA
  Instance: cdw
  Max Parallelism: 0
  EXPORT Job Parameters:
  State: DEFINING
  Bytes Processed: 0
  Job Error Count: 0

Export> kill
Are you sure you wish to stop this job ([yes]/no):

---

I've looked at the Metalink resources, but no clue found there

Best regards
Remigiusz Sokolowski

-- 
Pole na kazi

----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
pos   : Senior DBA at DIiUSI
comp  : Nordea AB oddział w Polsce
addr  : Luzycka 6A st, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
geo   : 54.4952N 18.4332E




.
NORDEA Bank A.B. Spółka Akcyjna, Smalandsgatan 17, SE 105 71 Sztokholm Szwecja, Oddział w Polsce, Aleja Edwarda Rydza Śmigłego 20
93-281 Łódź Polska, wpisany do rejestru przedsiębiorców prowadzonego przez Sad Rejonowy dla Łodzi Śródmieścia w Lodzi Wydział Krajowego Rejestru Sadowego pod numerem KRS 0000360 398; NIP 105-000-11-08; Regon 100926668; kapitał zakładowy w wysokości  4.049.951.919 EUR, wpłacony w całości
NORDEA IT,  adres do  korespondencji    81-537 Gdynia, ul. Łużycka 6a, Polska

NORDEA Bank A.B. Company Limited by Shares, Smalandsgatan 17, SE 105 71 Stockholm Sweden, Branch in Poland having its main office at
Aleja Edwarda Rydza-Smiglego 20, 93-281 Lodz Poland  registered in the Provisional Court for Lodz Srodmiescie in Lodz Business Division  of the National Court Register at: KRS 0000360398; NIP (Tax Identification Number) 105-000-11-08; share capital in amount of 4.049.951.919 EUR (fully covered)
NORDEA IT,  address for correspondence   81-537 Gdynia, ul. Łużycka 6a, Poland
.
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 19 2015 - 12:47:51 CET