Rosetta service pack 5.2 ends with warning related to the script rep_DPS_16142.sql
- Product: Rosetta
- Product Version: 5.2
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:
- ssh to one of Rosetta servers with dps user
- 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; /
- 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