ORA-20011（外部表收集统计信息异常报错）

报错现象

Wed Dec 25 22:09:16 2019

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.

Errors in file /export/u01/app/oracle/diag/rdbms/epmfdp/epmfdp2/trace/epmfdp2_j002_16017.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文件

ORACLE_HOME = /export/u01/app/oracle/product/11g/db_1

System name:    SunOS

Node name:      t5b01

Release:        5.11

Version:        11.1

Machine:        sun4v

Instance name: epmfdp2

Redo thread mounted by this instance: 2

Oracle process number: 167

Unix process pid: 16017, image: oracle@t5b01 (J002)

*** 2019-12-25 22:09:16.334

*** SESSION ID:(1405.17503) 2019-12-25 22:09:16.334

*** CLIENT ID:() 2019-12-25 22:09:16.334

*** SERVICE NAME:(SYS$USERS) 2019-12-25 22:09:16.334

*** MODULE NAME:(DBMS_SCHEDULER) 2019-12-25 22:09:16.334

*** ACTION NAME:(ORA$AT_OS_OPT_SY_18029) 2019-12-25 22:09:16.334

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.

*** 2019-12-25 22:09:16.334

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OGG"','"ET$04BC4D1F0001"','""', ...)

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.

 

*** 2019-12-25 22:09:16.377

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OGG"','"ET$110638090002"','""', ...)

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.

 

*** 2019-12-25 22:09:16.431

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OGG"','"ET$031C841D0001"','""', ...)

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.

 

*** 2019-12-25 22:09:17.182

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"OGG"','"ET$0A49B1890001"','""', ...)

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.

DBMS_STATS:  Too many errors...  Rest of the erros are not reporded.

DBMS_STATS: GATHER_STATS_JOB: Stopped by Scheduler.

 

--下面是因为超过工作日统计信息收集时长

*** 2019-12-26 02:00:23.690

*** CLIENT ID:() 2019-12-26 02:00:23.690

 

DBMS_STATS: Consider increasing the maintenance window duration if this happens frequently.

DBMS_STATS: The following objects/segments were not analyzed due to timeout:

 

*** 2019-12-26 02:00:26.087

DBMS_STATS:   TABLE: "CACHER01"."ARC_E_CONS_SNAP_TEST"."P32401"

DBMS_STATS:   TABLE: "CACHER01"."ARC_E_CONS_SNAP_TEST"."P32402"

DBMS_STATS:   TABLE: "CACHER01"."ARC_E_CONS_SNAP_TEST"."P32403"

DBMS_STATS:   TABLE: "CACHER01"."ARC_E_CONS_SNAP_TEST"."P32407"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_DETAIL"."LIST_1111111111111111118"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_DETAIL"."LIST_1111111111111111119"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_DETAIL"."LIST_1111111111111111126"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_VALUE_DETAIL"."LIST_1111111111111111116"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_VALUE_DETAIL"."LIST_1111111111111111132"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_VALUE_DETAIL"."LIST_1111111111111111133"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_VALUE_DETAIL"."LIST_1111111111111111134"

DBMS_STATS:   TABLE: "EXCHANGER30"."DATA_DIFF_RESULT_VALUE_DETAIL"."LIST_1111111111111111135"

确认库中表信息

select *

  from dba_objects t

 where t.OBJECT_NAME like 'ET%'

   and t.OWNER = 'OGG'

 order by t.CREATED;

dba_external_tables

这些表都是外部表，查询也报错

SQL> select count(*) from OGG.ET$04BC4D1F0001;

select count(*) from OGG.ET$04BC4D1F0001

*

ERROR at line 1:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump

job.

问题原因

由于数据库对ogg模式名下的外部表（ET$开头）收集统计信息时异常而报错

解决方法

删除报错的外部表

drop table OGG.ET$04BC4D1F0001;