ORA-39083: Object type INDEX failed to create with error: ORA-14102: only one LOGGING or NOLOGGING clause may be specified

Error Message

When performing an Oracle database export with the expdp of Oracle 11gR2 (11.2.0.1) and then importing it into the database with impdp, the following error messages appear in the import log file:

"ORA-39083: Object type INDEX failed to create with error:

ORA-14102: only one LOGGING or NOLOGGING clause may be specified

Failing sql is:
CREATE UNIQUE INDEX <schema>.<Index name> ON <schema>.<table name> (<columns>) PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING NOCOMPRESS LOGGING STORAGE( INITIAL 393216 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "<TS Name>" PARALLEL 1"

If the index was a compressed index, the following error messages appear in the import log file:

"ORA-39083: Object type INDEX failed to create with error:

ORA-02158: invalid CREATE INDEX option
Failing sql is:

CREATE UNIQUE INDEX <schema>.<Index name> ON <schema>.<table name> (<columns>)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1 NOCOMPRESS NOLOGGING TABLESPACE "<TS Name>"

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type <schema>.<Index name> creation failed"

Cause

This is a known Oracle 11gR2 issue.

The problem is that the DBMS_METADATA.GET_DDL returns invalid syntax for an index created. So, during the index creation, both the NOLOGGING and LOGGING keywords are visible in the DDL.

For more information on this known Oracle 11gR2 issue, see Oracle Problem [ID 1066635.1].

Solution or Workaround