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

When trying to rebuild an index, the following error occured:

SQL> ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online;
ALTER INDEX myschema.I_MV_CTR_ADVERT_ID rebuild online
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

If the index is based on an mview, then the following error can also occur when trying to refresh the mview

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-08102: index key not found, obj# 4145783, file 172, block 364789 (3)
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 2

This can also be related to ORA-08104: this index object 4145783 is being online built or rebuilt

Metalink gives some useful info on the ORA-01452 error – ORA-01452: Cannot Create Unique Index; Duplicate Keys Found [ID 332494.1]

Run the following to find duplicate rows

-- Script find duplicate rows
select  
--Enter name(s) of column(s) which should be unique
&&c 
from 
-- Enter name of table with duplicate rows
&&t
where rowid not in (select min(rowid) from &&t group by &&c)
Advertisements

Block Corruption – ORA-08102: index key not found

We had a refresh job which started failing with the following error:

Error at line 2
ORA-12008: error in materialized view refresh path ORA-08102: index key not found, obj# 6104122, file 364, block 27923 (2) ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2545
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2751
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2720
ORA-06512: at line 2

ORA-08102 is a mismatch between the key(s) stored in the index and the values stored in the table.

To resolve i first found out which object was affected:

select owner, object_name, object_type
from dba_objects
where object_id = 6104122;

Which returned the schema.I_MY_OBJECT

This was an index so then the next step was to rebuild the index

ALTER INDEX SCHEMA.I_MY_OBJECT rebuild;

but this didn’t resolve the error. Research found the following:

Rebuilding index will not work on most (if not in all) cases. Rebuilding index does not visit table at all. It uses existing index structure, to create new segment, so this problem will be transferred to new index as well.

In the end i dropped and recreated the index which seemed to resolve the error.