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)
About these ads

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

  1. peter oyeti says:

    i tried to solve the problem by searching for multiple index with the same name, but there wasnt.
    what can i do please

    • dbamohsin says:

      Hi Peter,
      Its not multiple indexes with the same name, it is duplicate data with the same uniqueness which can happen in some circumstances. For example if an index\constraint was disabled and data entry had taken place to allow the duplication.

      Use the syntax given above by finding your unique columns on the index that is giving the error (&&c), and the table name which the index sits on (&&t)

      Let me know if you find your duplicates. Also check out the metalink article.

  2. peteroyeti@aol.com says:

    - 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)

    after running the above syntax, it didnt return any result , just empty table. what do you think i should do?

  3. peter oyeti says:

    after running the scripts, it returned the coulmns, but with no data
    what can i do?

  4. dbamohsin says:

    Make sure that index isnt being unknowingly rebuilt in the background or has hung rebuilding. (https://dbamohsin.wordpress.com/2011/09/15/ora-08104-this-index-object-4145783-is-being-online-built-or-rebuilt/)

    Do check out the Metalink note ID 332494.1 for reasons why this error occurs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 129 other followers

%d bloggers like this: