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:
declare
l_h number;
begin
l_h:=dbms_datapump.attach('YOUR_JOB');
dbms_datapump.stop_job(l_h,immediate=>1);
commit;
end;
/
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:
declare
invalid_arg exception;
pragma exception_init(invalid_arg,-39001);
:
:
begin
:
:
exception when invalid_arg then
dbms_datapump.detach(l_h);
end;
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
:
: