526互联

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

发布时间 2023-11-10 09:01:49作者: syksky

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.