Datapump cleanup

Sometimes if you’ve been building data pump jobs via PL/SQL, you might get some part of it incorrect, and thus the job is left in the state of “DEFINING”, ie, you are building it but never managed to complete the process.  An interesting anomaly is that when this happens, your current session struggles to clean things up, ie,


SQL> select owner_name, job_name, state 
  2  from dba_datapump_jobs;

OWNER_NAME                      JOB_NAME                 STATE
-----------------------------   ----------------------   -----------------------------
SCOTT                           EMP_EXP                  DEFINING     


SQL> declare
  2      l_datapump_handle    NUMBER;
  3      l_datapump_dir       VARCHAR2(20) := 'DATAPUMP_DIR';
  4  begin
  5          l_datapump_handle := dbms_datapump.attach(job_name => 'EMP_EXP');
  6          dbms_datapump.detach(handle => l_datapump_handle);
  7
  8  END;
  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 1470
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4455
ORA-06512: at line 5

At which point you’re thinking – what’s wrong ? I can see my job in the dictionary, why can’t I access it ? The first thing you should try is a new session.


SQL> conn scott/tiger
Connected.
SQL> declare
  2      l_datapump_handle    NUMBER;
  3      l_datapump_dir       VARCHAR2(20) := 'DATAPUMP_DIR';
  4  begin
  5          l_datapump_handle := dbms_datapump.attach(job_name => 'EMP_EXP');
  6          dbms_datapump.detach(handle => l_datapump_handle);
  7
  8  END;
  9  /

PL/SQL procedure successfully completed.



Simple 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s