Ora 29400 data cartridge error

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

The pair ORA-29913/ORA-29400 is a sort of catch-all exception embedding KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.
The type of errors encountered spans from syntax errors to missing files or privileges.

For instance, yesterday i got this one when i forgot to specify a keyword in the external table definition.

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);
select * from IMP_BAD_BOXES;

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "logfile": expecting one of: "column, ..."
KUP-01007: at line 1 column 1

I marked in red color the position in the statement that caused the run-time error.
Note indeed that when you create the table, no issues are reported, you won’t know if it works until you go live.

So, in the end, this verbose error message was to report that i forgot to specify the keyword RECORDS before LOGFILE.
If you look at the syntax diagram of the ACCESS PARAMETERS clause (ver. 10R1), you’ll notice that there are four distinct sub-clauses.
LOGFILE belongs to the record format sub-clause. This means that you cannot specify any keyword in this sub-clause if you haven’t specified the RECORDS keyword first.
Note also that from a syntax standpoint it is perfectly legitimate to write the RECORDS keyword alone, but if you do not add DELIMITED BY NEWLINE ( or some other specification) then the record terminator will remain undefined, resulting in the following run-time error:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04038: internal error: unknown record type

So, my original statement must be rewritten as:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);

But how do i put the log file in directory other than IMPORT_DIR?
While the official documentation states that one can write a file location as directory:filename, in the reality it turns out that one must enclose the file name in single quotes, otherwise the following syntax error is returned:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
( IMPORT_DIR:BOXES.dat)
);

ORA-00905: missing keyword

On the other hand, if you put the directory specifier inside the quotes too, you’ll get the following run-time error:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
('IMPORT_DIR:BOXES.dat')
);

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file IMPORT_DIR:BOXES.dat in LOG_DIR not found

Finally, here is the correct syntax in blue color:

CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION
(IMPORT_DIR:'BOXES.dat')
);

It is up to you to decide whether you want to make the DEFAULT DIRECTORY the place where the source file is read from or the folder where the log files are written to.
Depending on the situation, you may need the appropriate READ and WRITE privileges on it.

As a last note, in case you wonder what would happen if you ALTER the table instead of dropping and re-creating it, you may want to know that it would perfectly possible to execute a statement like this:

ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'boxes.log');

however this statement will wipe out the previous definition of ACCESS PARAMETERS, because it doesn’t add just a LOGFILE, but replaces the ACCESS PARAMETERS as a whole.
So, don’t forget to include the whole sub-clause again if you plan to use ALTER TABLE, as follows:

ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
);

Finally, let me provide a full-fledged example of external table where every log file goes to its own place:

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY IMPORT_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE LOG_DIR:'BOXES.log'
DISCARDFILE DISCARD_DIR:'DISCARDS.log'
BADFILE BAD_DIR:'BAD.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED       POSITION(1:14)  CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID       POSITION(15:22) CHAR,
DEPT          POSITION(23:24) CHAR)
)
LOCATION ('BOXES.dat');

As you can see i specified the DEFAULT DIRECTORY clause without embedding the directory object name in double quotes. You must use double quotes if the directory object name is case sensitive otherwise always use uppercase letters.
Do not use single quotes for the directory object name or you’ll get ORA-22929.

See message translations for ORA-29400 and search additional resources.

ORA-20011 ORA-29913 and ORA-29400 with KUP-04040 Errors from DBMS_STATS.GATHER_STATS_JOB

<<Back to DB Administration Main Page

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/diag/rdbms/orcl1d/ORCL1D/trace/ORCL1D_j000_24858.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file PSG_XVD.csv in DUMP_DATA not found
Tue Sep 10 22:05:06 2019


Cause
The primary cause of this issue is that an OS file for an «external table» existed at some point in time but does not now. However, the database still believes the OS file for the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.


Solution
Clean up the orphaned dictionary entries.

For Temporary Datapump External Table

check out the following document from oracle support and follow the steps accordingly.

Document 10327346.8 Bug 10327346 — DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)
Document 336014.1 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

Other External Table

With cases where specific External tables (be they Demo Schema tables or other tables) are missing, the process for handling them is much the same and can be resolved by following the procedures below. For example, if the additional error is ‘error opening file ../demo/schema/log/ext_1v3.log’, then this indicates that there is a problem opening or locating the log file from the demo schema directory. The implication is that the demo tables have not been cleared up correctly:
Locate the files for these tables in their directory.

undefine owner
undefine table_pattern

select el.table_name, el.owner, dir.directory_path||’/’||dir.directory_name «path»
from dba_external_locations el
, dba_directories dir
where el.table_name like ‘%&&table_pattern%’
and el.owner like ‘%&&owner%’
and el.directory_owner = dir.owner
and el.directory_name = dir.directory_name
order by 1, 2;

• 
  It may be that the files still exist but they have just been renamed or re-located.
• It may be that the directory path itself does not exists on the server.

If file has been renamed or re-located you can restore back the file to avoid the problem.

If the file has been removed or the directory itself has been deleted then follow either the following steps:

Lock the statistics on these tables by using the following command:

DBMS_STATS.LOCK_TABLE_STATS (‘HR’,’EMP’);

OR
Remove the dictionary object for the external table. DROP TABLE HR.EMP;

More on locking unlocking Stats read here 

I was doing import of a table and started noticing the below error. Even though the database was same.


Tried different methods but it didn’t work. In Oracle support it was mentioned that I may be hitting a bug  and i may have to set NLS_LENGTH_SEMANTICS to char at DB level which was set to byte.


I noticed the table carefully and saw it has a dependency on a Oracle TYPE. 


When i saw its script saw that it was varchar2(20)


CREATE OR REPLACE TYPE ORAUSER1.OFFSET_HDR IS VARRAY(50) of VARCHAR2(20); 

SO I created it manually with an option of «char« in datatype.



SQL> CREATE OR REPLACE TYPE ORAUSER2.OFFSET_HDR IS VARRAY(50) of VARCHAR2(20 char)
  2  /


Type created.


Post that the table was imported successfully.

oracle@linux01:/home/oracle(ORADB)$ impdp / directory=DB_EXP_MOUNT dumpfile=exp_365137_2_%U.dmp logfile=imp_365137_2.log remap_schema=ORAUSER1:ORAUSER2 transform=oid:n parallel=4 cluster=no


Import: Release 12.1.0.2.0 — Production on Thu Jul 12 23:14:07 2018


Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 — 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Advanced Analytics, Oracle Database Vault and Real Application Testin
Master table «OPS$oracle».»SYS_IMPORT_FULL_05″ successfully loaded/unloaded
Starting «OPS$oracle».»SYS_IMPORT_FULL_05″:  /******** directory=DB_EXP_MOUNT dumpfile=exp_365137_2_%U.dmp logfile=imp_365137_2.log remap_schema=ORAUSER1:ORAUSER2 transform=oid:n parallel=4 cluster=no parfile=exclude_procobj.par table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table «ORAUSER2″.»RBX_CURVES» exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object «ORAUSER2″.»RBX_CURVES» failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type «ORAUSER2″.»OFFSET_HDR» not found or conversion to latest version is not possible
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job «OPS$oracle».»SYS_IMPORT_FULL_05″ completed with 1 error(s) at Thu Jul 12 23:14:10 2018 elapsed 0 00:00:02