Monday, November 18, 2013

ORA-14064: Index with Unusable partition exists on unique/primary constraint key

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14064: Index with Unusable partition exists on unique/primary constraint key
Failing sql is:...

hit above error with datapump import.

check STATUS in dba_ind_partitions , where some partitions are in  UNUSABLE state.

select index_owner,index_name, PARTITION_name,status from dba_ind_partitions where  status <>'USABLE' ORDER BY 1;


P2013_09_19                    USABLE
P2013_09_22                    USABLE
P2013_10_01                    UNUSABLE
P2013_10_07                    USABLE
P2013_10_11                    USABLE
P2013_10_14                    UNUSABLE
P2013_10_19                    USABLE

...

rebuild the index partition

Select 'ALTER INDEX '||index_owner||'.'|| index_name ||' rebuild partition ' || PARTITION_NAME ||';' from dba_ind_partitions where  status <>'USABLE' ORDER BY 1;

re-export the dump.