##########################
## Error
##########################
ORA-14063: Unusable index exists on unique/primary constraint key
### Full Error
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14063: Unusable index exists on unique/primary constraint key
Failing sql is:
ALTER TABLE "ABC_OWNER"."ABC_TABLE" ADD CONSTRAINT "ABC_TABLE_PK" PRIMARY KEY ("RULE_NAME") USING INDEX "ABC_OWNER"."ABC_TABLE_PK" ENABLE
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-14063: Unusable index exists on unique/primary constraint key
##########################
# Error Occurred
##########################
Error occured while doing a full import in 11.2.0.4.0 version
##########################
## Command Executed
##########################
Schema level import command.
**************************************** Step By Step Analysis ******************************************************
#########################################
# 1) Check index status in Source Database
#########################################
#### Check the indexes which are not VALID in Source Database.
SET LINES 200
col owner for a15
col index_type for a15
col tablespace_name for a15
col table_owner for a15
Select owner,index_name,index_type,table_owner,table_name,table_type,tablespace_name,status,LAST_ANALYZED from dba_indexes where STATUS='UNUSABLE';
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE TABLESPACE_NAME STATUS LAST_ANAL
--------------- ------------------------------ --------------- --------------- ------------------------------ ----------- --------------- ---------- ---------
ABC_OWNER ABC_TABLE_PK NORMAL ABC_OWNER ABC_TABLE TABLE IDX_TS1 UNUSABLE
=====================================================================================================================
#########################################
# 2) Reason for Failure
#########################################
Error is occuring because in the source database itself the indexes are in UNUSABLE state, so when we are trying to import in target database it fails to get created.
=====================================================================================================================
##########################
## Solution
##########################
Rebuild all the UNUSABLE indexes in source database and then perform an export in source and import in target.
@@@@ Rebuild Indexes
set pages 100
select 'Alter index ' || owner||'.' || index_name ||' rebuild online parallel 8;' from dba_indexes where STATUS='UNUSABLE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINEPARALLEL8;'
----------------------------------------------------------------------------------------------------
Alter index ABC_OWNER.ABC_TABLE_PK rebuild online parallel 8;
=====================================================================================================================
After this import worked good.
=====================================================================================================================
=====================================================================================================================
Comments Are Always welcome
=====================================================================================================================
