DBMS_STATS ORA-20011 Approximate NDV failed ORA-29913 error in executing ODCIEXTTABLEOPEN callout
目录1、现象
alert.log
Sun Nov 05 22:06:23 2023
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /oracle/diag/rdbms/ffpdbhis/FFPDBHIS/trace/FFPDBHIS_j000_48670.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
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.
*** 2023-11-05 22:06:23.152
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYSTEM"','"ET$000A099D0001"','""', ...)
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.
2、分析
外部表没有正常删除，数据字典中还有信息残留，表实际已不存在。
当数据库运行收集统计信息job时收集这些外部表时就报错。
3、解决
1.查看表信息
SQL> col OBJECT_NAME for a30
SQL> select owner,
2 object_name,
3 object_type,
4 status,
5 to_char(created, 'yyyy-mm-dd hh24:mi:ss') created,
6 to_char(last_ddl_time, 'yyyy-mm-dd hh24:mi:ss') last_ddl_time
7 from dba_objects
8 where object_name like 'ET$%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME
------------------------------ ------------------------------ ------------------- ------- ------------------- -------------------
SYSTEM ET$000A099D0001 TABLE VALID 2018-10-30 16:18:45 2018-10-30 16:18:45
2.确认属于DataPump的表
select owner,table_name,default_directory_name,access_type from dba_external_tables order by 1,2;
3.删除残留表
SQL> drop table SYSTEM.ET$000A099D0001;
Table dropped.