DBMS_STATS: GATHER_STATS_JOB encountered errors

一、在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

 

三、原因及解決辦法

 

參考

ORA-29913, ORA-29400, KUP-04040 While Selecting from External Table (Doc ID 150737.1)

 

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 ;

 

OWNER         DIRECTORY_NAME         DIRECTORY_PATH
------        --------------         -----------------------
SYS           EMP_DIR                /oradata/external_files

     


$ 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';

OWNER     TABLE_NAME     LOCATION    DIRECTORY_OWNER     DIRECTORY_NAME
-----     -------------  --------    ---------------     ---------------
SYS       EMPXT          emp1.dat    SYS                 EMP_DIR
SYS       EMPXT          emp2.dat    SYS                 EMP_DIR

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

SQL> select * from sys.empxt; 

EMPNO     ENAME     JOB        MGR     HIREDATE    SAL   COMM   DEPTNO
------    -----     --------   -----   ---------   ----  ------ ------
7369     SMITH      CLERK      7902    17-DEC-80   150   0       20
7499     ALLEN      SALESMAN   7698    20-FEB-81   150   0       30
7521     WARD       SALESMAN   7698    22-FEB-81   150   0       30
...

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27348/viewspace-763778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27348/viewspace-763778/