Oracle Database Discussions

Hello All,

I am using Oracle 11.2.0.3

in my alert logs I found the below errors:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

to resolve that i did the below steps:

SELECT owner_name, job_name, operation, job_mode, 
state, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2; 

the result was that there is two running jobs and the attached_session flag was equal to 1, something like the below:

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      SYS_IMPORT_TABLE_05      IMPORT TABLE RUNNING        1
SCOTT      SYS_IMPORT_TABLE_01 IMPORT TABLE     RUNNING        1 

so i stopped these 2 jobs using the below statements:

SET serveroutput on 
SET lines 100 
DECLARE 
   h1 NUMBER; 
BEGIN 
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_TABLE_05','SCOTT'); 
   DBMS_DATAPUMP.STOP_JOB (h1); 
END; 
/
SET serveroutput on 
SET lines 100 
DECLARE 
   h1 NUMBER; 
BEGIN 
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_TABLE_01','SCOTT'); 
   DBMS_DATAPUMP.STOP_JOB (h1); 
END; 
/

now the result become like below as stop pending in the state column:

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      SYS_IMPORT_TABLE_05      IMPORT TABLE STOP PENDING 1
SCOTT      SYS_IMPORT_TABLE_01 IMPORT TABLE     STOP PENDING 1 

for that I did more research and i dropped two temporary external tables after I got their names using the below query:

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/

but nothing changed in the output of the below query, the state stayed STOP PENDING

SELECT owner_name, job_name, operation, job_mode, 
state, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2; 

for that I used stop job but with the immediate flag as 1

 DBMS_DATAPUMP.STOP_JOB (h1,1); 

now the result of the query is NOT RUNNING in the state column:

OWNER_NAME JOB_NAME            OPERATION JOB_MODE  STATE       ATTACHED
---------- ------------------- --------- --------- ----------- --------
SCOTT      SYS_IMPORT_TABLE_05      IMPORT TABLE     NOT RUNNING   0
SCOTT      SYS_IMPORT_TABLE_01      IMPORT TABLE     NOT RUNNING   0 

and I discovered that one of my import jobs that I did not realize that it was running, failed due to a fatal error, it is the one related to the import of one table.

My question:

Now the below query should return zero records no ?
How i can clear the result ? and does it affect any future import jobs for the same table?

SELECT owner_name, job_name, operation, job_mode, 
state, attached_sessions 
FROM dba_datapump_jobs 
WHERE job_name NOT LIKE 'BIN$%' 
ORDER BY 1,2;