ORA-00001: unique constraint violated error when running the CALC_HISTORY_STATISTICS utility job

Products

• CA Automic Applications Manager (AM)

Issue/Introduction

Received a unique constraint violated error when running the CALC_HISTORY_STATISTICS utility job.

Environment

Application Manager V 9.6.2

Cause

Product Error

Resolution

This issue is identified as an error from the Product, a code change is required to fix this error.

The code change/fix will be released in future releases of appworx having version higher than 9.6.2,  to ensure the rows inserting into the table will not have duplicated rows.

 

For workaround you can follow below steps:

The current primary key need to be recreated with a name 

1 find the tablespace name where indexes are created

SQL> select TABLESPACE_NAME from user_indexes where table_name ='AW_REPORTS_HISTORY';

Write down the tablespace name xxxx

 

2 Drop current unnamed primary key

SQL> alter table aw_reports_history drop primary key 

3 create a named primary key with the tablespace name xxxx returned in step 1  
alter table aw_reports_history add constraint pk_aw_reports_history primary key
(dayid,hourid,report_type,groupby_type,groupby_id)
using index tablespace xxxx

4 deploy the new package

SQL>@aw_reports_pkg_de180896.plb

 

And test run the job CALC_HISTORY_STATISTICS. Suggest to stop AM to do the steps.