Oracle DBA - Rajabaskar Blog: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Daily we received the below information error in alert log on QA/PERF/Production databases. Database running on Linux 64 bit server and version is 11.2.0.2.

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

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

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

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

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

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

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

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

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

RAJA ET$000400390001 TABLE VALID 12-may-2011 09:02:55 12-may-2011 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_

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

RAJA ET$000400390001 EXP_DIR1 CLOB

RAJA ET$001802470001 EXP_DIR1 CLOB

RAJA ET$001A18EF0001 EXP_DIR1 CLOB

RAJA ET$004300030001 EXP_DIR1 CLOB

RAJA ET$004300050001 EXP_DIR1 CLOB

RAJA ET$005800070001 EXP_DIR1 CLOB

RAJA ET$005800090001 EXP_DIR1 CLOB

RAJA ET$009802870001 EXP_DIR1 CLOB

8 rows selected.

4. To Drop the external temporary datapump tables.

SQL> drop table RAJA.&tbl_name;

Enter value for tbl_name: ET$000400390001