ORA-29913, ORA-29400, KUP-04040 While Selecting from External Table

刘泽宇

现象：
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

or:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file <file_name> in <directory_name> not found

原因：
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.

解决方法：
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.