oracle 39083,impdp with ORA-39083: Object type INDEX_STATISTICS failed to create with error

version(source,destination): 11.2.0.4

impdp导入数据时报错:

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

ORA-39083: Object type INDEX_STATISTICS failed to create with error:

ORA-01403: no data found

ORA-01403: no data found

Failing sql is:

DECLARE I_N VARCHAR2(60);   I_O VARCHAR2(60);   NV VARCHAR2(1);   c DBMS_METADATA.T_VAR_COLL;   df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';  stmt varchar2(300) := ' INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES (''I'',6,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,NULL,:14,:15,NULL,:16,:17)';BEGIN  DELETE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

参考

DataPump Import (IMPDP) Failed With Errors ORA-39083 ORA-1403 On INDEX_STATISTICS (文档 ID 755253.1)

导致错误的根本原因:存在包含primary key的所有columns 的其它索引

例如:

create table texp1 (id number ,name varchar2(10) );

alter table texp1 add primary key (id,name);

create index ind_texp1 on texp1(name,id);  ---与pk索引的顺序不同

impdp时加exclude=statistics虽然不再报错，但是会丢失SYS_Cxxx的索引

另一种解决方法:

在源库修改SYS_Cxxx索引的名字, alter index SYS_C005657 rename to ind1_texp1;

但是，导入目标库后drop constraint不会同时删除这个索引，因为导入目标库后ind$.property是0,不是4097

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/37279/viewspace-2126182/，如需转载，请注明出处，否则将追究法律责任。