报错现象
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;