Duplicate Key Error During Upgrade of Enterprise Analyzer Workspace

Problem:

Client receives error during attempted upgrade of workspace in Enterprise Analyzer.  Upgrade log returns following message: [Oracle][ODBC][Ora]ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
.  While upgrading UpgradeModel> <code: Fatal>

Resolution:

This error results from duplicate keys stored in a specific EA table.  The table is usually identified in the few lines in the log leading up to the error message.  A SQL query can be used to identify and remove the duplicates to allow the upgrade to proceed.

The query below can be modified for any table.  In the example below, the table is RelProgramusesPsbModule.

delete from RelProgramUsesPsbModule

where ROWID in (select RID

                   from (select ROWID as RID,

                                leftobject,

                                rightobject,

                                ROW_NUMBER() OVER(PARTITION BY leftobject, rightobject order by leftobject, rightobject) as RNK

                           from RelProgramUsesPsbModule) TMP1

                  where TMP1.RNK > 1)

/

COMMIT

/