Rel 12.2 driver fails at cnsca.odf

Start time for statement below is: Fri Mar 31 2023 14:45:10

CREATE UNIQUE INDEX CN.”CN_SCA_RULE_ATTRIB_ALL_TL_U2″ ON

 CN.CN_SCA_RULE_ATTRIBUTES_ALL_TL (SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE)

 LOGGING STORAGE (FREELIST GROUPS 4 FREELISTS 4 )  PCTFREE 10 INITRANS 11

 MAXTRANS 225 COMPUTE STATISTICS   TABLESPACE APPS_TS_SEED

Statement executed.

AD Worker error:

The index cannot be created as the table has duplicate keys.

Solution:

During Upgrade to 12.2.0, Script CNSCA.ODF Fails – Create Index CN_SCA_RULE_ATTRIB_ALL_TL_U2 – The index cannot be created as the table has duplicate keys. (Doc ID 1674721.1)

SQL> SELECT SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE, count() FROM CN.CN_SCA_RULE_ATTRIBUTES_ALL_TL WHERE ORG_ID IS NULL GROUP BY SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE HAVING count()>1 2 3 4 5 ;

SCA_RULE_ATTRIBUTE_ID ORG_ID LANG COUNT(*)

            -1020            PTB          14
            -1019            PTB          14
            -1009            PTB          14
            -1003            PTB          14
            -1021            PTB          14
            -1018            PTB          14
            -1014            PTB          14
            -1008            PTB          14
            -1001            PTB          14
            -1000            PTB          14
            -1016            PTB          14

SCA_RULE_ATTRIBUTE_ID ORG_ID LANG COUNT(*)

            -1015            PTB          14
            -1011            PTB          14
            -1002            PTB          14
            -1023            PTB          14
            -1013            PTB          14
            -1012            PTB          14
            -1007            PTB          14
            -1006            PTB          14
            -1005            PTB          14
            -1017            PTB          14
            -1022            PTB          14

SCA_RULE_ATTRIBUTE_ID ORG_ID LANG COUNT(*)

            -1010            PTB          14
            -1004            PTB          14

24 rows selected.

SQL> SELECT SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE, count() FROM CN.CN_SCA_RULE_ATTRIBUTES_ALL_TL WHERE ORG_ID IS NOT NULL GROUP BY SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE HAVING count()>1 2 3 4 5
6 ;

no rows selected

SQL> CREATE table cn_sca_rule_attrib_all_tl_bkp AS
SELECT * FROM cn_sca_rule_attributes_all_tl; 2

Table created.

SQL> DELETE FROM cn_sca_rule_attributes_all_tl WHERE org_id IS NULL;

336 rows deleted.

SQL> commit;

Commit complete.

SQL>

Metalink Reference:

During Upgrade to 12.2.0, Script CNSCA.ODF Fails – Create Index CN_SCA_RULE_ATTRIB_ALL_TL_U2 – The index cannot be created as the table has duplicate keys. (Doc ID 1674721.1)