首页
登录 | 注册

expdp/impdp ORA-39083 ORA-14102


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 Patch 8795792

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

REFERENCES

 

相关文章

  • RUEI 13c 安装
    在虚拟机上做安装练习,重点参考官方文档,但官方文档中也有些错误 操作系统版本 CentOS 6.5  RUEI 版本 13.2.3.1 ORACLE版本 11.2.0.4 前提: 700G空间 16G内存 网卡静态IP地址 配置NTP服务 ...
  • 9.You ran this command on a source database: $> expdp hr/hr DIRECTORY=dumpdir DUMPFILE=empl.dmp VIEWS_AS_TABLES=emp_d ...
  • 一,安装clamav 根据系统的不同,用不同的命令,yum,pacman等.如果找不到包的话,只有下源码自己编译了. freshclam 三,查看一下clamav进程 四,一些常规用法举例 2,将扫描结果存放到log中,clamscan - ...
  • ORA-12514: TNS:listener does not currently know of service requested in connect 配置data guard网络监听的时候,连接测试报错如下:ERROR:ORA-1 ...
  • 经常做恢复验证,每次都有些小不同,想省点事,搞个一键还原可好?适用于不同实例,源端和目标端最好版本一致,平台一致,开始吧. 步骤介绍: #适用oracle 11g单机环境 #备份在192.168.1.1的RESTORDIR目录下,采用rma ...
  • Oracle 12C Sharding部署和测试
    Oracle 12C Sharding部署和测试 日期:2019-02-26 作者:陈举超   一:环境说明 OS:CentOS Linux release 7.5 DB:Oracle 12.2.0.1.0 GSMOCI:2.2.1 本次使 ...

2020 unjeep.com webmaster#unjeep.com
12 q. 0.012 s.
京ICP备10005923号