http://www.itpub.net/showthread.php?s=&postid=7168709#post7168709
今天遇到一个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
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正常。
使用10.2.0.2.0的impdp版本问题消失！看来是oracle的BUG。
