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.
If you encounter these errors in the alert.log of an instance , usually the cause is that an OS file for an external table existed at some point in time but does not now, but the database still believes the OS file for the table exists.
When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails.
Error KUP-11024 is for temporary Datapump external tables that have not been cleaned up properly.
Ensure that there are no DataPump jobs running at the same time as the DBMS_STATS job run.
The run as sysdba:
1. SQL to identify the external table name and the owner:
to_char(created,'dd-mon-yyyy hh24:mi:ss') created ,
to_char(last_ddl_time , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
object_name like 'ET$%' ;
"OWNER" "OBJECT_NAME" "OBJECT_TYPE" "STATUS" "CREATED" "LAST_DDL_TIME"
"SYSTEM" "ET$00F9E3ED0001" "TABLE" "VALID" "11-dec-2012 10:59:30" "11-dec-2012 10:59:30"
2. SQL to check the dirctory the DB thinks the external table is
select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
ORDER BY 1,2
3. Drop the table
drop table SYSTEM.ET$00F9E3ED0001 purge;