一、在alert.log文件中發現如下錯誤 ：

*** 2013-06-01 10:00:13.500
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"ALERT_HUMAN2"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: 執行 ODCIEXTTABLEOPEN 對外呼叫時發生錯誤
ORA-29400: 資料匣錯誤
KUP-04040: 找不到在 alert_human2.log 中的檔案 BDUMP

 

二、主機OS及DB版本信息

　　

　　Red Hat Enterprise Linux Server release 6.2 (Santiago)

　　DB為四節點的RAC，版本信息如下：　
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

 

三、原因及解決辦法

 

參考

 

Applies to:

Oracle Server - Enterprise Edition - Version 9.0.1.0 and later
Information in this document applies to any platform.
***Checked for relevance on 30-Jul-2012***

Symptoms

When selecting from an external table , you get the following error:

SQL> select * from sys.empxt;
select * from sys.empxt
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp1.dat in EMP_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

When analyzing the table, you get a similar message:

SQL> execute sys.dbms_stats.gather_table_stats('SYS','EMPXT');
BEGIN sys.dbms_stats.gather_table_stats('SYS','EMPXT'); END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp1.dat in EMP_DIR not found
ORA-06512: at "SYS.DBMS_STATS", line 7161
ORA-06512: at "SYS.DBMS_STATS", line 7174
ORA-06512: at line 1

Cause

The flat files associated to the external table (emp1.dat, emp2.dat) do not exist in the OS directory pointed by the logical directory EMP_DIR.

Solution

Copy/move/recreate the flat file emp1.dat so that it exists in the OS directory pointed by the logical EMP_DIR directory.

SQL> select * from dba_directories ;

 

     


$ mv /tmp/emp1.dat /oradata/external_files

If the problem still persists:

SQL> select * from sys.empxt;
select * from sys.empxt
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp2.dat in EMP_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

then be sure that all OS flat files associated to the external table exist in the OS directory pointed by the logical EMP_DIR directory:

SQL> select * from dba_external_locations
2 where table_name='EMPXT';

$ mv /tmp/emp2.dat /oradata/external_files

SQL> select * from sys.empxt; 

SQL> execute sys.dbms_stats.gather_table_stats('SYS','EMPXT');
PL/SQL procedure successfully completed.