ORA-29400: data cartridge error

I got ” ORA-29400: data cartridge error ”  error in Oracle database.

ORA-29400: data cartridge error

Details of error are as follows.

ORA-29400: data cartridge error
Cause: An error has occurred in a data cartridge external procedure. This message will be
 followed by a second message giving more details about the data cartridge error.
Action: See the data cartridge documentation for an explanation of the second error message.

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

data cartridge error

This ORA-29400 is related with the error has occurred in a data cartridge external procedure. This message will be
followed by a second message giving more details about the data cartridge error.

See the data cartridge documentation for an explanation of the second error message.

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.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )