How to determine, ora-00001: unique constraint error, is due to oracle sequence issue, while creating new SCM object like project,user,version etc?

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

Description:

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. In SCM r 12.0, we have about 17 sequences as evident in user_sequences view.

But if sequence counter generates already existing OBJID then we get ora-00001: unique constraint error. Then we will check and ensure the latest number sequence would generate is higher than the current max OBJID [which is also the latest] of the corresponding SCM object.

So, we could run following sql and compare:

1. Select SEQUENCE_NAME,LAST_NUMBER from user_sequences;

   SEQUENCE_NAME                  LAST_NUMBER ------------------------------ ----------- HARAUDITEVENTSEQ                n HARBRANCHSEQ                    n HARENVIRONMENTSEQ               n HARFORMATTACHMENTSEQ            n HARFORMSEQ                      n HARITEMSSEQ                     n HARITEMNAMESEQ                  n HARPACKAGEGROUPSEQ              n HARPACKAGESEQ                   n HARPROCESSSEQ                   n HARREPOSITORYSEQ                n HARSTATESEQ                     n HARUSERGROUPSEQ                 n HARUSERSEQ                      n HARVERSIONDATASEQ               n HARVERSIONSSEQ                  n HARVIEWSEQ                      n 

   17 rows selected.
   
   
2. Following list maximum [latest] OBJID from corresponding tables in same matching order as above query.

   select max(branchobjid) from harbranch;select max(envobjid) from harenvironment;select max(attachmentobjid) from harformattachment;select max(formobjid) from harform;select max(itemobjid) from haritems;select max(pkggrpobjid) from harpackagegroup;select max(packageobjid) from harpackage;select max(processobjid) from harstateprocess;select max(repositobjid) from harrepository;select max(stateobjid) from harstate;select max(usrgrpobjid) from harusergroup;select max(usrobjid) from haruser;select max(versiondataobjid) from harversiondata;select max(versionobjid) from harversions;select max(viewobjid) from harview;select max(auditeventobjid) from harauditevent;select max(nameobjid) from haritemname; 

3. Then we compare and ensure LAST_NUMBER value [here "n"] is higher than maximum [latest] OBJID from corresponding tables.
   Furthere, we may even get specific error for example -> ora-00001: unique constraint (SCM12.HARVERSIONDATA_PK) violated ..
   
   Then we could check value relevant to harversiondata tables and sequence. But overal list as above may help to verify all since such sequence corruption may affect other sequences as well.
   
   If above query show no problem then we could check for other issue like orphan record, duplicate name etc which may give similar error as well.

Solution:

Once we identified the problem is due to sequence then ORACLE DBA with proper privilege can update and ensure that LAST_NUMBER for corresponding SEQUENCE_NAME is higher than max [latest] objid of the SCM object we are creating.

Environment

Release:
Component: HDATA