Sunday, February 21, 2016

ORA-14063: Unusable index exists on unique/primary constraint key


##########################
## 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
=====================================================================================================================



No comments: