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

Version 2

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    Remedy AR System Server


    AR System Server


    BMC Remedy AR System Server version 9.0 and higher.



    While trying to index a form customer getting following error - 


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




    Action 1:

    The definitin of this is as follows:

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

    Cause: A CREATE UNIQUE INDEX statement specified one or more columns that currently contain duplicate values. All values in the indexed columns must be unique by row to create a UNIQUE INDEX.

    Action: If the entries need not be unique, remove the keyword UNIQUE from the CREATE INDEX statement, then re-execute the statement. If the entries must be unique, as in a primary key, then remove duplicate values before creating the UNIQUE index

    There is also another one related to an after commit function.

    DROP INDEX I2844_1000000182_1
    <SQL > <TID: 1098692928> <RPC ID: 0001505329> <Queue: Admin > <Client-RPC: 390620 > <USER: tzacis > <Overlay-Group: -1 > /* Wed Feb 06 2013 16:23:00.6051 */*** ERROR *** ORA-01418: specified index does not exist

    It reads as follows:

    ORA-01418: specified index does not exist

    Cause: An ALTER INDEX, DROP INDEX, or VALIDATE INDEX statement specified the name of an index that does not exist. Only existing indexes can be altered, dropped, or validated. Existing indexes may be listed by querying the data dictionary.

    Action: Specify the name of an existing index in the ALTER INDEX, DROP INDEX, or VALIDATE INDEX statement.

    So this one suggest that the previous action to drop an index did not commit affectively. Ensure you are creating a clustered index from scratch and any changes to existing indexes are committed first in the server before proceeding on to other actions.

    after doing the action 1 is the same issue then follow the action 2:

    Action: 2:=====

    ARServer "thinks" that there are indices that exist, that really don't. The easiest way to rectify this problem is to shut down ARServer, delete the entries for the indices from your schema_index table that do not exist in your table (and not vice-versa...). After that, restart the AR Server. The indices will need to be recreated using DevStudio/Admin tool.

    Important: please check with your DBA the operations mentioned above.


    Article Number:


    Article Type:

    Solutions to a Product Problem

      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles