Creating Index APPLSYS FND_FORM_CUSTOM_SCOPES_U1 Fails With "Table has Duplicate keys" Error
(Doc ID 1591975.1)
Last updated on DECEMBER 22, 2023
Applies to:
Oracle Applications DBA - Version 12.2 to 12.2 [Release 12.2Cloud]Information in this document applies to any platform.
Symptoms
The R12.2 upgrade script fnd_form_custom_scopes.xdf fails with:
The error is able Name is FND_FORM_CUSTOM_SCOPES
Table exists in the target database
Checking for differences
Number of columns for the table in the xml file is 9
The table in the Xml file and in the target database match
Executing grants on FND_FORM_CUSTOM_SCOPES to APPS
GRANT ALL ON APPLSYS.FND_FORM_CUSTOM_SCOPES TO APPS WITH GRANT OPTION
Synonyms match.
Index hashcode(s) extracted from the XDF.
Hashcodes generated for DB indexes.
Index FND_FORM_CUSTOM_SCOPES_N1 exists under APPLSYS schema.
Checking for differences
Column_name and Uniqueness match for both file and DB Indexes
Index FND_FORM_CUSTOM_SCOPES_U1 does not exist in APPLSYS.
CREATE UNIQUE INDEX "APPLSYS"."FND_FORM_CUSTOM_SCOPES_U1" ON "APPLSYS"."FND_FORM_CUSTOM_SCOPES" ("RULE_ID", "LEVEL_ID", "LEVEL_VALUE", "LEVEL_VALUE_APPLICATION_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"
Start time for statement above is Sun Oct 13 12:21:51 PDT 2013
The Index Cannot be created as the Table has Duplicate keys <<<<<
Table exists in the target database
Checking for differences
Number of columns for the table in the xml file is 9
The table in the Xml file and in the target database match
Executing grants on FND_FORM_CUSTOM_SCOPES to APPS
GRANT ALL ON APPLSYS.FND_FORM_CUSTOM_SCOPES TO APPS WITH GRANT OPTION
Synonyms match.
Index hashcode(s) extracted from the XDF.
Hashcodes generated for DB indexes.
Index FND_FORM_CUSTOM_SCOPES_N1 exists under APPLSYS schema.
Checking for differences
Column_name and Uniqueness match for both file and DB Indexes
Index FND_FORM_CUSTOM_SCOPES_U1 does not exist in APPLSYS.
CREATE UNIQUE INDEX "APPLSYS"."FND_FORM_CUSTOM_SCOPES_U1" ON "APPLSYS"."FND_FORM_CUSTOM_SCOPES" ("RULE_ID", "LEVEL_ID", "LEVEL_VALUE", "LEVEL_VALUE_APPLICATION_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"
Start time for statement above is Sun Oct 13 12:21:51 PDT 2013
The Index Cannot be created as the Table has Duplicate keys <<<<<
Use the following SQL stmt to indentify the duplicate keys
SELECT RULE_ID , LEVEL_ID , LEVEL_VALUE , LEVEL_VALUE_APPLICATION_ID , COUNT(*) FROM APPLSYS.FND_FORM_CUSTOM_SCOPES GROUP BY RULE_ID , LEVEL_ID , LEVEL_VALUE , LEVEL_VALUE_APPLICATION_ID HAVING COUNT(*) > 1
SELECT RULE_ID , LEVEL_ID , LEVEL_VALUE , LEVEL_VALUE_APPLICATION_ID , COUNT(*) FROM APPLSYS.FND_FORM_CUSTOM_SCOPES GROUP BY RULE_ID , LEVEL_ID , LEVEL_VALUE , LEVEL_VALUE_APPLICATION_ID HAVING COUNT(*) > 1
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |