Error message in WLF_LIST_ALIASES.log :

The following index(es) on table WLF_LIST_ALIASES were processed:

index WLF_APP_PRD.ENTRY_ALIAS_UPPER_IDX partition P6700 loaded successfully with 825 keys

index WLF_APP_PRD.SYS_C00166789 was made unusable due to:

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

 

Troubleshoot

1. Check unusable index in table with below query :

SELECT owner, index_name, tablespace_name 

FROM dba_indexes WHERE  status = 'UNUSABLE';

and


SELECT index_owner, index_name, partition_name, tablespace_name FROM   dba_ind_PARTITIONS WHERE  status = 'UNUSABLE';


2. Check by partition index, which entity duplicate and causing index to be unusable

SELECT DISTINCT LIST_ENTRY_ALIAS_ID,LIST_VERSION_IDENTIFIER,LIST_ENTRY_KEY,COUNT(LIST_ENTRY_KEY)

FROM WLF_LIST_ALIASES 

PARTITION(P6700) 

GROUP BY LIST_ENTRY_ALIAS_ID,LIST_VERSION_IDENTIFIER,LIST_ENTRY_KEY

HAVING COUNT(LIST_ENTRY_KEY)>1;


3. Check duplicate key 

SELECT * FROM WLF_APP_PRD.WLF_LIST_ALIASES

WHERE LIST_ENTRY_KEY in ('3681820'

,'3748327') 

AND LIST_VERSION_IDENTIFIER ='6700'

AND LIST_ENTRY_ALIAS_ID = '1'

and rownum < 3;


4. Check processed file for duplicate Entity_ID, and remove it from file

 

Corrective Action

1. Disable unique constraint 

2. Delete row duplicate 

3. Enable unique constraint 

4. Rebuild the unusable index partition 

           


5. Rerun previous process having issue