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

 Alert log File information:

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_22426.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.

Trace File Information:

*** 2015-03-26 08:30:18.342
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OPS$ORACLE"','"ET$016FA3770001"','""', ...)
DBMS_STATS: 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.


Cause:
The primary cause of this issue is that an external table existed at some point in time but does not now. However, the database still believes the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.

There are many reasons that an external table may not exist including:
1. Temporary Data pump external tables have not been cleaned up properly. The dictionary information should have been dropped when the DataPump jobs completed.
2. An External table has been removed without clearing up the corresponding data dictionary information. For example: Oracle Demo Schema Tables such as the external table “SALES_TRANSACTIONS_EXT” may have been removed but the dictionary has not been updated to reflect this. The "SALES_TRANSACTIONS_EXT" table is an external table in the "SH" schema which is one of Demo Schema provided by Oracle.

Note:Our issue is due to point one. External tables are not cleaned properly.

Solutions:  To clean up the Orphaned datapump jobs.

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;


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

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
------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------
OPS$ORACLE                     ET$000E4FF90001                TABLE                     VALID   16-oct-2012 13:10:15      16-oct-2012 13:10:15
OPS$ORACLE                     ET$007360190001                TABLE                     VALID   18-sep-2012 23:17:32      18-sep-2012 23:17:32
OPS$ORACLE                     ET$00F39F430001                TABLE                     VALID   16-oct-2012 13:33:10      16-oct-2012 13:33:10
OPS$ORACLE                     ET$016FA3770001                TABLE                     VALID   16-oct-2012 13:57:36      16-oct-2012 13:57:36

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_
------------------------------ ------------------------------ ------------------------------ -------
OPS$ORACLE                     ET$000E4FF90001                PRODUCT_REFRESH_DIR            CLOB
OPS$ORACLE                     ET$007360190001                PRODUCT_REFRESH_DIR            CLOB
OPS$ORACLE                     ET$00F39F430001                PRODUCT_REFRESH_DIR            CLOB
OPS$ORACLE                     ET$016FA3770001                PRODUCT_REFRESH_DIR            CLOB


To Drop the external temporary datapump tables.
SQL> drop table OPS$ORACLE.ET$000E4FF90001;
SQL> drop table OPS$ORACLE.ET$007360190001;
SQL> drop table OPS$ORACLE.ET$00F39F430001;
SQL> drop table OPS$ORACLE.ET$016FA3770001;
5.To ensure there is no datapump temporary tables.

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

no rows selected

SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE from dba_external_tables order by 1,2
 /


Reference doc   1274653.1 

티스토리 툴바