Patch Failed Due To Some Duplicate Data. Error message is: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found (Doc ID 2296450.1)

Last updated on AUGUST 11, 2017

Applies to:

Oracle U.S. Federal Financials - Version 12.2.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.3 version, Federal Setup

Patch 25485888:R12.FV.C failed due to some duplicate data


ERROR
-----------------------
Index FV_BE_RPR_TRANSACTIONS_U1 exists under FV schema.
Checking for differences
Column_name and Uniqueness do not match for both file and DB Indexes
Creating Revised index
Drop query is DROP INDEX FV."FV~BE~RPR~TRANSACTIONS~U1"
Drop query is DROP INDEX FV."FV~BE~RPR~TRANSACTIONS~U1"
Error in executing statement: CREATE UNIQUE INDEX "FV"."FV~BE~RPR~TRANSACTIONS~U1" ON "FV"."FV_BE_RPR_TRANSACTIONS" ("BUDGET_LEVEL_ID", "DOC_NUMBER#1", "SET_OF_BOOKS_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"
  ONLINE
Error message is: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


CREATE UNIQUE INDEX "FV"."FV~BE~RPR~TRANSACTIONS~U1" ON "FV"."FV_BE_RPR_TRANSACTIONS" ("BUDGET_LEVEL_ID", "DOC_NUMBER#1", "SET_OF_BOOKS_ID")
  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 16384 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
  TABLESPACE "APPS_TS_TX_IDX"
  ONLINE
Start time for statement above is Thu Feb 23 22:20:27 EST 2017
The Index Cannot be created as the Table has Duplicate keys
Use the following SQL stmt to indentify the duplicate keys
SELECT BUDGET_LEVEL_ID , DOC_NUMBER#1 , SET_OF_BOOKS_ID , COUNT(*) FROM FV.FV_BE_RPR_TRANSACTIONS GROUP BY BUDGET_LEVEL_ID , DOC_NUMBER#1 , SET_OF_BOOKS_ID HAVING COUNT(*) > 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms