ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
September 15, 2011 5 Comments
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)