Fixing Oracle Database ORA-29913 Error

Using External Tables is becoming more and more popular, eliminating the need of SQL*Loader to load tables. However, you also face issues while working with external tables, one of which is described here:

When you perform a SELECT operation on an external table, you may face something similar to the following:

“ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXTERN_17063.log
KUP-04040: file TEST.TXT in TEST_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 19”

The most common cause of the ORA-29913 error is that Oracle fails to open the OS files it needs. These files may include input files, output files and the log, and bad or discard files. The reason behind such behavior may be Oracle executable has not been granted the permission for accessing the directory properly or the directory does not exist or the file is not granted the permissions.

You may also find in the error line that Oracle fails to write to the log file; it may be an issue with the directory or with the .log file in that directory.

Therefore, when you try to work with external tables, you need to perform a few checks that are listed below:

• Check the statements that you have used for creating directory.
• Check that the directories are granted the necessary rights or permissions for Oracle to perform read/write to them.
• Perform a special check on ownership and permissions for network directories.
• Check that there is no problem associated with the permissions for existing files.

Well, you can fix the problem by trying out some suggested solutions that are as follows:

1. Build physical directories at all nodes of the cluster and copy the physical data files to all the locations.
2. While you present the storage from SAN or NAS, it can be presented as NFS. Note that it also mandates a separate license from storage provider.
3. Use a Cluster file system at OS level that is visible from all nodes. However, the solution is a bit expensive, as you need to purchase an additional license.
4. When you access the external tables, get connected to a specific instance, but you will not be able to take advantage of services.

This is what you need to do to fix the issue, however, whatever you choose to perform, make sure that it is being carried out carefully and efficiently that results in no further issues with the Oracle database.