My Oracle Support Banner

External table creation during Import of an 11.2 dumpfile into 12.2 and above is failing with ORA-00931: missing identifier. (Doc ID 2770109.1)

Last updated on MAY 03, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.

Symptoms

The external table creation during impdp is failing with ORA-00931: missing identifier.
All the tables that failed during import are external tables ( ORGANIZATION EXTERNAL ).
It is due to wrong ddl being generated by impdp. It is related to the NULL location clause in external table ddl.

Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"<schemaname>"."<tablename>" failed to create with error:
ORA-00931: missing identifier

Failing sql is:
CREATE TABLE "<schemaname>"."<tablename>" (<Columnlist>)
ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "RCMS_BATCH_IN" ACCESS PARAMETERS ( records delimited by newline
NOBADFILE NODISCARDFILE NOLOGFILE
LOAD WHEN(record_ind = 'H' or record_ind = 'F')
FIELDS LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
<columnlist>
)
) LOCATION ( ) ) REJECT LIMIT UNLIMITED

Changes

 Import of an 11.2 dumpfile into 12.2 and above

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.