重庆思庄Oracle、Redhat认证学习论坛

标题: ORA-29913, ORA-29400, KUP-04040 While Selecting from External Table [打印本页]

作者: 刘泽宇 时间: 2024-3-31 11:21
标题: 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.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)