Wednesday, August 31, 2016

ORA-20011 & KUP-11024 during DBMS_STATS: GATHER_STATS_JOB

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. 

Solution:

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:

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"
"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
from dba_external_tables
ORDER BY 1,2
;

3. Drop the table 

drop table SYSTEM.ET$00F9E3ED0001 purge;