一:系统环境
操作系统:
uname -a
AIX egapdb2 1 6 00F81BFA4C00
数据库:
11.2.0.3.5
二:错误信息
在alert.log发现如下错误信息:
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /apps/oracle/diag/rdbms/egapdb/egapdb2/trace/egapdb2_j003_13369700.trc:
ORA-20011: Approximate NDV failed: ORA-06564: object EXPDDDP does not exist
trace文件中信息如下:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1
System name: AIX
Node name: egapdb2
Release: 1
Version: 6
Machine: 00F81BFA4C00
Instance name: egapdb2
Redo thread mounted by this instance: 2
Oracle process number: 780
Unix process pid: 13369700, image: oracle@egapdb2 (J003)
*** 2014-07-07 22:00:17.682
*** SESSION ID:(1514.32367) 2014-07-07 22:00:17.682
*** CLIENT ID:() 2014-07-07 22:00:17.682
*** SERVICE NAME:(SYS$USERS) 2014-07-07 22:00:17.682
*** MODULE NAME:(DBMS_SCHEDULER) 2014-07-07 22:00:17.682
*** ACTION NAME:(ORA$AT_OS_OPT_SY_16083) 2014-07-07 22:00:17.682
ORA-20011: Approximate NDV failed: ORA-06564: object EXPDDDP does not exist
*** 2014-07-07 22:00:17.682
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"EGCMS"','"ET$0B72AAD30001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-06564: object EXPDDDP does not exist
*** 2014-07-07 22:00:17.708
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"EGCMS"','"ET$044324A10001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-06564: object EXPDDDP does not exist
*** 2014-07-07 22:00:17.719
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"EGCMS"','"ET$070CA05B0001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-06564: object EXPDDDP does not exist
*** 2014-07-07 22:00:17.729
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"EGCMS"','"ET$0D4D92ED0001"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-06564: object EXPDDDP does not
##"ET$XXXX"是系统自动命名的外部表表名(datapump的使用可能会产生系统命名的外部表)
关于DataPump的external_table模式可以参见Maclean Liu的博客:
三:问题分析
1.进行如下信息查询
conn / as sysdba
set pages 100
set num 20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set mark html on
spool info.html
select * from dba_external_tables order by 1,2;
select * from dba_directories;
select * from dba_objects where object_name like 'ET$%' or object_name='EXPDDDP';
select * from dba_datapump_jobs where job_name not like 'BIN$%' order by 1, 2;
select o.status, o.object_id, o.object_type, o.owner || '.' || object_name "OWNER.OBJECT" from dba_objects o, dba_datapump_jobs j where o.owner = j.owner_name and o.object_name = j.job_name and j.job_name not like 'BIN$%' order by 4, 2;
spool off
exit
上面脚本查得的信息请见附件info.html(下载链接如下)
ORA-20011_ORA-06564_info.html-Oracle其他资源-CSDN下载
(CSDN博客不能上传附件,只能放在资源里,有点不方便啊!如果需要看的朋友就去资源里下载吧,没办法)
通过dba_external_tables查询信息我们可以判断"ET$XXXX"表是由datapump任务产生的外部表,外部表的路径为EXPDDDP
通过dba_directories视图查询发现系统中已经不存在EXPDDDP路径(可能是任务后被删除),所以alert日志会报ORA-06564: object EXPDDDP does not exist
。但是这并不是此错误的根本原因(根本原因是“ETXXXX”外部表没有被清除)
通过dba_datapump_jobs视图发现有两个not running的import任务,从dba_objects视图可以查看到两张同datapump任务名相同的表SYS_IMPORT_TABLE_02,SYS_IMPORT_TABLE_01(master 表)
##由上面的综合分析我们可以得出问题的原因,即datapump任务产生的临时表不知是什么原因,没有被清除。导致GATHER_STATS_JOB在收集该表统计信息时报错
SQL> select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_tab_statistics where table_name like 'ET%';
OWNER TABLE_NAME NUM_ROWS LAST_ANALYZE STA
---------- -------------------- ---------- ------------ ---
EGCMS ET$044324A10001
EGCMS ET$070CA05B0001
EGCMS ET$072384130001
EGCMS ET$0B72AAD30001
EGCMS ET$0D4D92ED0001
从dba_tab_statistics表中可以得出这些外部表确实需要收集统计信息。
##但是具体是什么原因导致datapump任务产生的外部表没有被正常清除目前还不清楚,提了SR求助oracle原厂工程师,他们也没给出具体原因,只说可能是datapump任务被异常终止时会在dba_external_tables视图中出现“ETXXXX”表
##如果后面发现了具体原因会及时发布
四,解决方案
1.删除报错中涉及到的相关“ETXXXX”外部表
drop table EGCMS.ET$044324A10001 purge;
drop table EGCMS.ET$070CA05B0001 purge;
drop table EGCMS.ET$072384130001 purge;
drop table EGCMS.ET$0B72AAD30001 purge;
drop table EGCMS.ET$0D4D92ED0001 purge;
2.删除datapump任务的master表
select o.status, o.object_id, o.object_type, o.owner || '.' || object_name "OWNER.OBJECT" from dba_objects o, dba_datapump_jobs j where o.owner = j.owner_name and o.object_name = j.job_name and j.job_name not like 'BIN$%' order by 4, 2;
##注意做此操作时要确认被删除master表的不是暂停的,而是失败的不会再重新开始的任务
可以参考作者的另外一篇博客: