Rosetta service pack 5.2 ends with warning related to the script rep_DPS_16142.sql

Problem Symptom

Rosetta service pack 5.2 ends with warning related to the script rep_DPS_16142.sql.

install_sp.log (under $dps_dev/local_sp_install/5.2.0.0/log):

v249_rep00@DPS3> v249_rep00@DPS3> v249_rep00@DPS3> create unique constraint on collection name and parentId pair
DECLARE
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at line 11
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL_FAILED: /exlibris1/operational_shared/service_pack/download/5.20/build_dir_5.2.0.0/install_sp_dir/sql/structure_sql/5_2_0/WARN/rep_DPS_16142.sql

Cause

In version 5.2 unique constraint has been added to the collection table to prevent the creation of duplicate collections under race conditions. In cases there are duplicates, rep_DPS_16142.sql will fail with warning status. 

Resolution

Remove any collection naming duplicates (from Collection Management in the Rosetta UI) and run the script manually afterwards .

You may use the following script to identify the duplicates in HDECOLLECTION table:

SELECT id, name, parentid, owner FROM hdecollection t1 WHERE EXISTS (SELECT 1 from hdecollection t2 Where t1.name = t2.name AND NVL(T1.parentid,0)=NVL(t2.parentid,0) AND t1.id <> t2.id);

Run the rep_DPS_16142.sql script manually:

1. ssh to one of Rosetta servers with dps user
2. s+ rep00

DECLARE
 v_is_exists   INTEGER;
BEGIN
 SELECT COUNT ( * )
 INTO v_is_exists
 FROM user_indexes
 WHERE index_name = 'HDECOLLECTION_UK_03';
 IF (v_is_exists = 0) THEN
  DBMS_OUTPUT.PUT_LINE ('create unique constraint on collection name and parentId pair');
  EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX HDECOLLECTION_UK_03 ON HDECOLLECTION (NAME, PARENTID, OWNER)';
  EXECUTE IMMEDIATE 'ALTER TABLE HDECOLLECTION ADD CONSTRAINT HDECOLLECTION_UK_03 UNIQUE (NAME, PARENTID, OWNER) USING INDEX HDECOLLECTION_UK_03';
 END IF;
END;
/

4. The following output indicates a successful run:

create unique constraint on collection name and parentId pair
PL/SQL procedure successfully completed.

• Article last edited: 05-April-2017