Posted by 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 🙂
Learning is not a spectator sport 