Chinaunix首页 | 论坛 | 博客
  • 博客访问: 283023
  • 博文数量: 27
  • 博客积分: 368
  • 博客等级: 一等列兵
  • 技术积分: 491
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-09 21:35
个人简介

再出发..

文章分类

全部博文(27)

文章存档

2018年(1)

2014年(6)

2013年(5)

2012年(15)

我的朋友

分类: Oracle

2014-02-24 15:37:17


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

 
阅读(7125) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~