Oracle 11g 数据泵 PK / compress /impdp/

来源：互联网发布：.tv域名火爆编辑：程序博客网时间：2024/06/10 17:00

今天遇到一个impdp的问题。

如果PK索引包含compress参数，会导致的是否无法导入。

select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected.

CREATE table t as SELECT * FROM dba_objects where rownum<=10;

CREATE UNIQUE INDEX PK_T ON T (OWNER, OBJECT_NAME) COMPRESS 1;

ALTER TABLE T ADD ( CONSTRAINT PK_T PRIMARY KEY (OWNER, OBJECT_NAME));

CREATE OR REPLACE DIRECTORY TMP AS 'd:/tmp';

GRANT READ, WRITE ON DIRECTORY TMP TO SCOTT WITH GRANT OPTION;

d:/> expdp scott/xxx directory=tmp dumpfile=scott.dmp tables=scott.T

DROP table t ;

d:/>impdp scott/xxx directory=tmp dumpfile=scott.dmp

ORA-39083: 对象类型 CONSTRAINT 创建失败, 出现错误:
ORA-14071: 用于强制约束条件的索引选项无效
失败的 sql 为:
ALTER TABLE "SCOTT"."T" ADD CONSTRAINT "PK_T" PRIMARY KEY ("OWNER", "OBJECT_NAME"

USING INDEX PCTFREE 10 INITRANS 2 MAX
TRANS 255 COMPRESS 1 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FR
EELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE

问题出在建立约束的时候加入了compress参数，删除这个参数执行sql正常。