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