Oracle DBA User Guide: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Solution for below information alert.

Alert log File information:

Mon Nov 28 22:00:10 2011

DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.

Errors in file /u01/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_14552.trc:

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.

I have checked the alert log and respective trace files. On Trace files, I found some information alert.

Trace File Information:

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"owner"','"tablename"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

I have checked metalink notes and got the solutions.

Why we getting this error everyday midnight?

1. While nightly stats gather job (DBMS_STATS) is running and when trying to gather statistics for temporary data pump external tables, we are getting the above error.

2. Temporary Datapump external tables have not been cleaned up properly. These should have been dropped when the DataPump jobs completed.

Note: while datapump running, oracle internally create master table for tracking and putting the datapump status. This master table helps to stop or start the datapump jobs.

Solutions: To clean up the Orphaned datapump jobs.

1. To ensure that there is no datapump jobs running at the same time as the dbms_stats job

2. To Check and cleanup orphaned datapump jobs.

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;

3. To identify the external tables.

SQL> conn / as sysdba

Connected.

SQL>

set linesize 200 trimspool on

set pagesize 2000

col owner form a30

col created form a25

col last_ddl_time form a25

col object_name form a30

col object_type form a25

SQL>

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$%'

OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME

------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------

PRAVEEN ET$001A18EF0001 TABLE VALID 18-may-2011 03:15:59 18-may-2013 03:15:59

PRAVEEN ET$009802870001 TABLE VALID 12-may-2011 09:26:44 12-may-2013 09:26:44

PRAVEEN ET$005800090001 TABLE VALID 12-may-2011 09:18:23 12-may-2013 09:18:23

PRAVEEN ET$005800070001 TABLE VALID 12-may-2011 09:16:42 12-may-2013 09:16:42

PRAVEEN ET$004300050001 TABLE VALID 12-may-2011 09:07:09 12-may-2013 09:07:09

PRAVEEN ET$004300030001 TABLE VALID 12-may-2011 09:06:07 12-may-2013 09:06:07

PRAVEEN ET$001802470001 TABLE VALID 12-may-2011 09:05:11 12-may-2011 09:05:11

PRAVEEN ET$000400390001 TABLE VALID 12-may-2011 09:02:55 12-may-2013 09:02:55

8 rows selected.

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables order by 1,2

OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_

------------------------------ ------------------------------ ------------------------------ -------

PRAVEEN ET$000400390001 EXP_DIR1 CLOB

PRAVEEN ET$001802470001 EXP_DIR1 CLOB

PRAVEEN ET$001A18EF0001 EXP_DIR1 CLOB

PRAVEEN ET$004300030001 EXP_DIR1 CLOB

PRAVEEN ET$004300050001 EXP_DIR1 CLOB

PRAVEEN ET$005800070001 EXP_DIR1 CLOB

PRAVEEN ET$005800090001 EXP_DIR1 CLOB

PRAVEEN ET$009802870001 EXP_DIR1 CLOB

8 rows selected.

4. To Drop the external temporary datapump tables.

SQL> drop table PRAVEEN.&tbl_name;

Enter value for tbl_name: ET$000400390001