Skip to main content
ExLibris

  • Subscribe by RSS
    • Rosetta
    Ex Libris Knowledge Center

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

    1. Last updated
    2. Save as PDF
    • 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:

    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;
/
    1. 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
    //Feedback