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

Below is the issue I faced a while back when GATHER STATS JOB was run so just wanted to share my knowledge how did I managed to resolve it.

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

ORA-29400: data cartridge error

Cause-: DBMS_STATS: GATHER_STATS_JOB encouters this error when it tries to gather stats on an external table as OS level file(test_file.csv) was not present inside directory "TEST_UTIL" created for this external table.

Solution-: After reviewing the trace file we found that OS level flat file(a csv file) was not present in the directory created for the external table so in order to resolve this issue either we need to place the relevant OS level flat file inside directory (TEST_UTIL) created for external table to be used or will have to remove/drop the external table to get this resolved.


Below is the query to list the external tables:
-------------------------------

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE

from dba_external_tables

order by 1,2

/

Note: This error can be encountered in some other cases like if Data Pump temporary table is left and was not cleaned up properly after export job was done.We will need to find the temporary table that is related to Data Pump Job using below query and purge that and finally re-execute the gather STATS job.

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

/

Drop the temporary tables that belong to the DataPump. eg:

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

SQL> drop table system.&1 purge;

Enter value for 1: ET$00654E1E0007

Feel free to contact me anytime....!!