Running upgrade UORAv560 570.sql fails with the following:
Error at line 3024
ORA-00001: unique constraint (STAT.CSR_PS_SWAT_SUBOBJ_PK1) violated ORA-06512: at line 3
RESOLUTION:
Run these two queries for field labels:
1. select count(*) SD_CD, CSR_ID, SWAT_ID, FIELDNAME, LABEL_ID
from STAT.ST_PSDBFLDLABL_8
group by SD_CD, CSR_ID, SWAT_ID, FIELDNAME, LABEL_ID
having count(*) > 1
2. SELECT COUNT(*), A.SD_CD, A.CSR_ID, A.SWAT_ID, A.LABEL_ID, A.FIELDNAME, B.OBJECT_NAME
FROM STAT.ST_PSDBFLDLABL_8 A
JOIN STAT.PSOBJ_DECODE B ON B.OBJECT_CLASS = 'P'
AND B.OBJECT_TYPE = '13'
AND B.OBJECT_NAME = A.FIELDNAME
GROUP BY A.SD_CD, A.CSR_ID, A.SWAT_ID, A.LABEL_ID, A.FIELDNAME, B.OBJECT_NAME
HAVING COUNT(*) > 1
Run these two queries for record fields:
1. select count(*) SD_CD, CSR_ID, SWAT_ID, RECNAME, FIELDNAME
from STAT.ST_PSRECFIELD_8F
group by SD_CD, CSR_ID, SWAT_ID, RECNAME, FIELDNAME
having count(*) > 1
2. SELECT COUNT(*), A.SD_CD, A.CSR_ID, A.SWAT_ID, A.RECNAME, A.FIELDNAME, B.OBJECT_NAME
FROM STAT.ST_PSRECFIELD_8F A
JOIN STAT.PSOBJ_DECODE B ON B.OBJECT_CLASS = 'P'
AND B.OBJECT_TYPE = '7'
AND B.OBJECT_NAME = A.RECNAME
GROUP BY A.SD_CD, A.CSR_ID, A.SWAT_ID, A.RECNAME, A.FIELDNAME, B.OBJECT_NAME
HAVING COUNT(*) > 1
If it returns results from running above queries, then see which CSRs# it referenced.
1. Delete all those archived set assosciated with output of above queries CSRs# using Archived Purge Wizard if they are old and you are no longer needed. Otherwise run the attached UMSSv560-570_DISTINCT.sql instead of UMSSv560-570.sql to resolve this problem.
2. Stat Web Client, Job monitor. Run the Purge Deleted Archive Sets
3. re-run the above Queries and make sure it returns 0 rows:
4. Re-run the upgrade script - UORAv560 570.sql as STAT schema user ID from TOAD or SQLPLus without getting the error -
Error at line 3024
ORA-00001: unique constraint (STAT.CSR_PS_SWAT_SUBOBJ_PK1) violated
ORA-06512: at line 3
