联系：手机/微信(+86 17813235971) QQ(107644445)
标题：impdp报ORA-39083 ORA-14102错误处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近两次遇到impdp 报 ORA-39083 ORA-14102错误
[oracle@localhost tmp]$ impdp "'/as sysdba'" directory=expdp_dir full=y dumpfile=1.dmp
Import: Release 11.2.0.1.0 - Production on Mon May 11 20:57:00 2026
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=expdp_dir full=y dumpfile=1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"AAA"."XXXXX" failed to create with error:
ORA-14102: only one LOGGING or NOLOGGING clause may be specified
Failing sql is:
CREATE TABLE "AAA"."XXXX" ("OBJECT_ID" NUMBER(9,0) NOT NULL ENABLE, "OBJECT_TYPE" VARCHAR2(8 BYTE) NOT NULL ENABLE
……………………
这个错误比较明显由于AAA.XXXX表的创建语句中有多于一个LOGGING or NOLOGGING,从而导致该创建语句无法正常创建表.
[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ oerr ora 14102 14102, 00000, "only one LOGGING or NOLOGGING clause may be specified" // *Cause: LOGGING was specified more than once, NOLOGGING was specified // more than once, or both LOGGING and NOLOGGING were specified. // *Action: Remove all but one of the LOGGING or NOLOGGING clauses and // reissue the statement.
查看该表对应的expdp导出日志
[oracle@xff expdmp]$ expdp "'/as sysdba'" tables="AAA"."XXXX" dumpfile=1.dmp compression=all EXCLUDE=STATISTICS,AUDIT Export: Release 11.2.0.4.0 - Production on Mon May 11 21:01:06 2026 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": tables=AAA.XXXX dumpfile=1.dmp compression=all EXCLUDE=STATISTICS,AUDIT Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3.207 GB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT . . exported "AAA"."XXXX":"F_GTG" 5.430 MB 968776 rows . . exported "AAA"."XXXX":"F_CONS" 4.734 MB 920007 rows …………
比较明显AAA.XXXX表是一个分区表,而且是从11.2.0.4中导出,然后准备导入到11.2.0.1版本数据库中.通过DBMS_METADATA.get_ddl来获取该表的ddl语句确实有多个LOGGING/NOLOGGING(主要是每个分区都有NOLOGGING)
SET ECHO OFF
SET PAGESIZE 0
SET LINES 3000
SET LONG 200000
SET FEEDBACK OFF
SET HEADING OFF
SET SERVEROUTPUT ON SIZE 1000000
COLUMN TXT FORMAT A3000 WORD_WRAPPED
SQL> SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','XXXX','AAA') TXT FROM DUAL;
CREATE TABLE "AAA"."XXXX"
( "OBJECT_ID" NUMBER(9,0) NOT NULL ENABLE,
"OBJECT_TYPE" VARCHAR2(8) NOT NULL ENABLE,
"DL_TYPE" VARCHAR2(8) NOT NULL ENABLE,
………………
CONSTRAINT "PK_OBJECT_DL_DEF" PRIMARY KEY ("OBJECT_ID", "OBJECT_TYPE", "DL_TYPE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY LIST ("OBJECT_TYPE")
(PARTITION "F_SUBS" VALUES ('1') SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ,
……………………
尝试在11.2.0.1环境中人工执行该sql创建表,直接报ORA-14020: 不能指定表分区的此物理属性错误
基于此基本上可以确认是由于11.2.0.1默认情况下不支持这样的语法,解决该问题的办法：
1. 重新导出来expdp dmp,加上version=11.2.0.1参数
2. impdp加上impdp TRANSFORM参数TRANSFORM=segment_attributes:n进行导入(impdp TRANSFORM参数)
