expdp/impdp ORA-39083 ORA-14102

7280阅读 0评论2014-02-24 iniestandroid
分类:Oracle


APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 27-MAR-2013***

SYMPTOMS

While performing DataPump import, errors are encountered during the index import phase:

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
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 . ON . () PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING NOCOMPRESS LOGGING STORAGE( INITIAL 393216 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "" PARALLEL 1

If the index was a compressed index, then the errors are:

ORA-39083: Object type INDEX failed to create with error:
ORA-02158: invalid CREATE INDEX option
Failing sql is:
CREATE UNIQUE INDEX . ON .
()
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1 NOCOMPRESS NOLOGGING TABLESPACE "
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type . creation failed

CAUSE

This is unpublished bug 9015411 which was closed as a duplicate of unpublished bug 8795792

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

For example:

CREATE UNIQUE INDEX . ON .
() PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGINGNOCOMPRESS LOGGING STORAGE( INITIAL 393216 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "" PARALLEL 1

This issue also holds true for compressed indexes where the DDL generated will contain both COMPRESS and NOCOMPRESS keywords in the syntax.

For example:

CREATE UNIQUE INDEX . ON .
()
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS 1 NOCOMPRESS NOLOGGING TABLESPACE ""

SOLUTION

1. If available for your platform, download and apply the 

Note: Please review the Readme file for instructions on how to install the patchset.

REFERENCES