How to resolve ORA-29913 with external tables

ORA-29913 is one of the common error when working with external tables in Oracle database. We would be looking at various ways the error can be produced and what can be done to resolve the error

(1) External File not found or permission incorrect

SQL> select * from Scott.example_external_table;
select * from example_external_table
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file test1.dat in TEST_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(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’);
BEGIN sys.dbms_stats.gather_table_stats(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’); END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file test1.dat in TEST_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 in general way

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

Reason

The external files have been moved from the correct location

SQL> select * from dba_directories ;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------ -------------- -----------------------
SYS TEST_DIR /u01/oradata/external_files

Check the File Names associated with external table

select * from dba_external_locations
where table_name='EXAMPLE_EXTERNAL_TABLE';OWNER TABLE_NAME LOCATION DIRECTORY_OWNER DIRECTORY_NAME
----- ------------- -------- --------------- ---------------
SYS EXAMPLE_EXTERNAL_TABLE test1.dat SYS TEST_DIR
SYS EXAMPLE_EXTERNAL_TABLE test2.dat SYS TEST_DIR

Now checking at the OS level
$ cd /u01/oradata/external_files
$ ls test[1-2]/dat
No such file or directory

So files are not present at the correct location

Both the above queries can be combined as below also

select TABLE_NAME, DIRECTORY_PATH||'/'||LOCATION file_name from DBA_EXTERNAL_LOCATIONS loc, dba_directories dir
where loc.DIRECTORY_OWNER=dir.OWNER
and loc.DIRECTORY_NAME=dir.DIRECTORY_NAME
and loc.OWNER='&1' and loc.TABLE_NAME='&2';

Resolution:
Move back both the files to the original location

$ mv /u02/oradata/external_files/test2.dat /u01/oradata/external_files
$ mv /u02/oradata/external_files/test1.dat /u01/oradata/external_files

Now
Both the below statement will succeed

select * from Scott.example_external_table;
execute sys.dbms_stats.gather_table_stats('SCOTT','EXAMPLE_EXTERNAL_TABLE');

(2) Incorrect data format in external file

ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached ORA-06512: at “SYS.ORACLE_LOADER”, line 14 ORA-06512: at line 1

Reason

(i) The external file may be having empty lines
(ii) Check the TAB delimiter or the HEX delimiter

a. The TAB delimiter ‘\t’, used by Oracle, is represented by a HEX value ’09’
b. The HEX value in the datafile should match the HEX value specified in the
CREATE statement
c. The HEX specification in the CREATE statement should look like 0X'<value&
gt;’ or 0x'<value>’
(iii) If the column added to the external table,same should be present in the external file

Resolution
Check the error in the log and correct the problem accordingly

(3) Error with Null column

ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source:

Reason
The external file does have null values for the last column which is originally a number column. When we change the null to a number 0,then the query succeeds.

Resolution
To load a NULL value,we need to include ENCLOSED BY delimiter in the table definition.

records delimited by newline fields terminated by ‘,’ optionally enclosed by ‘ ‘ <<<===== missing field values are null

(4) ORA-29913 can also occur if Oracle is not able to create the log files also.So check for the permission of the directories

(5) External table directory error
The ORA-29913 error can also happen in external tables when you don’t grant read and write permissions to the directory:

CREATE OR REPLACE DIRECTORY extdir AS ‘/u01/oradata/testdir’;
GRANT READ ON DIRECTORY testdir TO <user name>;
GRANT WRITE ON DIRECTORY testdir TO <user name>;

Here are some good information about External Table,You should  check out

What are EXTERNAL TABLES in Oracle?

(1)External tables are read only tables where the data is stored in flat files outside the database

(2) You can user external table feature to access external files as if they are tables inside the database.

(3)   When you create an external table, you define its structure and location with in oracle. Basically you just store the metadata inside the oracle

(4) When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

(5) The oracle server provides two major oracle driver to read the flat files

• Oracle_loader: This is used for the reading the flat files using the oracle loader technology. It basically allows to read the files which can be interpreted using sql loader technology
• Oracle_datapump: This is used for both importing and exporting of data using platform independent format

Related Articles
Oracle External tables :Check out this post for information on usage of external table in oracle with example, how to create external table, how to use it
Oracle Create table :Tables are the basic unit of data storage in an Oracle Database.we covers how to use Oracle create table command to create table with foreign key /primary key
ORA-00936 missing expression :Learn troubleshooting ORA-00936 missing expression in oracle SQL.what are various solution, how we can avoid it, Oracle bugs for this errors
ORA-01017: invalid username/password; logon denied :Learn the possible resolution of ORA-01017 invalid username/password; logon denied. How to resolve it quickly without muc efforts
ORA-00001 unique constraint violated :Check out this post for the possible solution for oracle error ORA-00001 unique constraint violated. How to resolve and fix it
ORA-00911: invalid character :This post is for common causes of ORA-00911: invalid character in oracle with examples and resolution to help you complete the job
ORA-00900 : This post for the various solutions for ORA-00900 invalid sql statement.Reasons for the error.How to debug and resolve it quickly,
ORA-03113: end-of-file on communication channel :Check out method to resolve ORA-03113: end-of-file on communication channel. This error could happen in database startup, running program
Oracle documentation

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning