Removing orphan DataPump jobs and filtering included tables with a query

November 20, 2010

DataPump, ORA-39001, ORA-39125, ORA-942

Say you define a DP job from the API (DBMS_DATAPUMP) you may end up with jobs with status NOT RUNNING until you get it right. Verify if you have such a job with

select job_name,state   
from user_datapump_jobs;  

Then you may try to remove it with:

 l_h number;  

If the query above still reports the job with the same status it can be removed by dropping the master table, according to Note 336014.1:

drop table YOUR_JOB;  

If the database is using a recycle bin the table has been renamed to a name starting with ‘BIN’; if you rerun the query above the job is renamed to reflect this, but if you purge the recycle bin the job will be finally removed:

purge recyclebin;  

If your PL/SQL code fails during definitions before you do a detach you cannot do a second attach. Since you have no reference to the job the only solution I’m aware of is to reconnect (i.e. create another database session). Better yet is to define an exception on ORA-39001 and retrieve the error message with dbms_datapump.get_status, and dbms_datapump.detach(l_handle) in the exception handler:

  invalid_arg exception;  
  pragma exception_init(invalid_arg,-39001);  
exception when invalid_arg then  

If you drop the master table that is in the defining state, you will not be able to attach to the job, but if you try to attach to such a job the job will be removed for the user_datapump_jobs table.

If you try to add a metadata filter of type NAME_EXPR and you add a subquery in the expression, make sure that any table name in the subquery is prefixed with table owner or else you may receive ORA-00942 in the logfile:

dbms_datapump.metadata_filter(handle => l_h, name => 'NAME_EXPR', value =>'IN (SELECT TABLE_NAME FROM SCOTT.CHANGE_TABLE)'  );   

Typical error in logfile when schema is missing:

Estimate in progress using BLOCKS method...  
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA  
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB \[\]  
ORA-00942: table or view does not exist  
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95  
ORA-06512: at "SYS.KUPW$WORKER", line 6307  
\----- PL/SQL Call Stack -----  
  object      line  object  
  handle    number  name  
0x7bfb80b0     15032  package body SYS.KUPW$WORKER