12.2. Upgrade Patch Fails on File csmcsma.odf - Index CSM_CNTR_RELATIONSHIPS_ACC - Table Has Duplicate Keys (Doc ID 2907199.1)

Last updated on JUNE 05, 2023

Applies to:

Symptoms

During R12.2 upgrade, file csmcsma.odf fails with the following error when creating index CSM_CNTR_RELATIONSHIPS_ACC_U2.

ERROR
-----------------------

CREATE UNIQUE INDEX CSM."CSM_CNTR_RELATIONSHIPS_ACC_U2" ON
CSM.CSM_CNTR_RELATIONSHIPS_ACC (RELATIONSHIP_ID) LOGGING STORAGE (FREELIST
GROUPS 4 FREELISTS 4 )  PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE
STATISTICS   TABLESPACE APPS_TS_TX_IDX

Statement executed.

AD Worker error:
The index cannot be created as the table has duplicate keys.

Use the following SQL statement to identify the duplicate keys:

SELECT RELATIONSHIP_ID, count(*)
FROM CSM.CSM_CNTR_RELATIONSHIPS_ACC
GROUP BY RELATIONSHIP_ID
HAVING count(*)>1

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Perform R12.2 upgrade (ref. Doc ID 1320300.1)
2. Statement is executed from file csmcsma.odf version 1:

  CREATE UNIQUE INDEX CSM."CSM_CNTR_RELATIONSHIPS_ACC_U2" ON
  CSM.CSM_CNTR_RELATIONSHIPS_ACC (RELATIONSHIP_ID) LOGGING STORAGE (FREELIST
  GROUPS 4 FREELISTS 4 ) PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE
  STATISTICS TABLESPACE APPS_TS_TX_IDX


 Upgrade to EBS 12.2

Changes

 Upgrade to EBS 12.2

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.