oracle中ORA-20011 KUP-11024错误分析
数据库alert日志出现ORA-20011 KUP-11024等错误
Thu Sep 22 18:00:31 2016
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.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.
从报错的信息看应该是数据库收集统计信息报错(GATHER_STATS_JOB)，但是报错原因是由于访问外部表导致,而该外部表很可能和data pump有关系.
查看trace日志
[oracle@xifenfei]$ more /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc
Trace file /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u1/oracle/pruduct/11.2.0.3
System name: Linux
Node name: xifenfei
Release: 2.6.32-220.el6.x86_64
Version: #1 SMP Wed Nov 9 08:03:13 EST 2011
Machine: x86_64
Instance name: xifenfei
Redo thread mounted by this instance: 1
Oracle process number: 356
Unix process pid: 2686, image: oracle@xifenfei (J002)
*** 2016-09-22 18:00:31.939
*** SESSION ID:(835.16363) 2016-09-22 18:00:31.939
*** CLIENT ID:() 2016-09-22 18:00:31.939
*** SERVICE NAME:(SYS$USERS) 2016-09-22 18:00:31.939
*** MODULE NAME:(DBMS_SCHEDULER) 2016-09-22 18:00:31.939
*** ACTION NAME:(ORA$AT_OS_OPT_SY_10669) 2016-09-22 18:00:31.939
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.
*** 2016-09-22 18:00:31.939
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DWDBA"','"ET$012D00070001"','""', ...)
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.
*** 2016-09-22 18:00:31.960
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DWDBA"','"ET$01D10D4F0001"','""', ...)
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.
通过trace文件,我们已经可以明确是由于数据库对DWDBA.ET$012D00070001和DWDBA.ET$01D10D4F0001这两个表收集统计信息时候报的上述alert日志中看到的错误.
查询数据库记录
SYS@xifenfei>select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
2 to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
3 ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
4 from dba_objects
5 where object_name like 'ET$%'
6 /
OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME
--------- ---------------- ------------ ------- ------------------------- ----------------
DWDBA ET$012D00070001 TABLE VALID 10-mar-2016 16:32:25 10-mar-2016 16:32:25
DWDBA ET$01D10D4F0001 TABLE VALID 10-mar-2016 17:29:29 10-mar-2016 17:29:29
SYS@xifenfei> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
2 from dba_external_tables
3 order by 1,2
4 /
OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_
----------- ------------------------------ ------------------------------ -------
DWDBA ET$012D00070001 EXP_FILE_DIR CLOB
DWDBA ET$01D10D4F0001 EXP_FILE_DIR CLOB
到这一步,我们已经完全清楚,ET$012D00070001和ET$01D10D4F0001是两个外部表,由于他们的存在使得收集统计信息异常。
分析ET$012D00070001表
SYS@xifenfei>desc DWDBA.ET$012D00070001
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
STORE_NO NUMBER(3)
ITEM_NO NUMBER(6)
WORK_DATE DATE
DIVISION_NO NUMBER(2)
SECTION_NO NUMBER(3)
SUP_NO NUMBER(6)
GRP_NO NUMBER(3)
SUBGRP_NO NUMBER(3)
USR VARCHAR2(30)
TYPE NUMBER(1)
ACTIVE_SELL_PRICE NUMBER(8,2)
SELL_PRICE NUMBER(8,2)
SELL_VAT NUMBER(1)
BUY_PRICE NUMBER(10,4)
BUY_VAT NUMBER(1)
PROMOTION_NO NUMBER(10)
PROM_CLASS VARCHAR2(1)
PROM_LEVEL NUMBER(1)
STOCK NUMBER(10,3)
STOCK_ADJ NUMBER(10,3)
RECPT NUMBER(10,3)
SALES NUMBER(10,3)
STOCK_ADJ_AMNT NUMBER(10,2)
RECPT_AMNT NUMBER(10,2)
SALES_AMNT NUMBER(10,2)
PROF_AMNT NUMBER(10,2)
COST_CHANGE NUMBER(10,2)
DISC NUMBER(10,3)
RTN_QTY NUMBER(9,3)
DISC_AMNT NUMBER(10,2)
RTN_AMNT NUMBER(10,2)
LOSS_AMNT NUMBER(10,2)
CREATED_DATE DATE
COST NUMBER(10,4)
NBR_PK NUMBER(5)
NBR_VISIT NUMBER(5)
NBR_PK_LINE NUMBER(5)
N_N_PROF_AMNT NUMBER(9,2)
CON_FORE NUMBER(10,2)
CON_FORE_OTH NUMBER(10,2)
SALES_B NUMBER(10,3)
SALES_AMNT_B NUMBER(10,2)
SYS@xifenfei>select count(*) from DWDBA.ET$012D00070001;
select count(*) from DWDBA.ET$012D00070001
*
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.
通过对ET$012D00070001表查询重新了alert日志一样的错误,可以明确定位问题就是由于该外部表异常导致.通过查询mos,确定Bug 10327346 DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)可能导致DBMS_WORKLOAD_CAPTURE无法正常清理掉Data pump的外部表导致出现Datapump出现孤立的外部表对象,从而出现该问题.解决方案就是直接drop 相关外部表.也就是这里的(ET$012D00070001和ET$01D10D4F0001)
原文来自http://www.xifenfei.com/2016/10/ora-20011-kup-11024.html
