search cancel

Clarity Load DWH job fails on UTL_RECOMP_COMP_IDX1

book

Article ID: 109728

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

When scheduling the Load Data Warehouse (DWH) job to run, it intermittently fails with the below error:
 
Couldn't execute SQL: BEGIN
DWH_CFG_POST_CONFIG_SP();
END;
 
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Unable to set values for index UTL_RECOMP_COMP_IDX1: does not exist or insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 439
ORA-06512: at "SYS.UTL_RECOMP", line 899
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 27901
ORA-06512: at "SYS.DBMS_STATS", line 27878
ORA-06512: at "SYS.DBMS_STATS", line 15441
ORA-06512: at "SYS.DBMS_STATS", line 27758
ORA-06512: at "SYS.DBMS_STATS", line 36332
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at "SYS.UTL_RECOMP", line 260
ORA-06512: at "SYS.UTL_RECOMP", line 803
ORA-06512: at "SYS.UTL_RECOMP", line 912
ORA-06512: at "SYS.DBMS_UTILITY", line 435
ORA-06512: at "PPM_DWH.DWH_CFG_POST_CONFIG_SP", line 5
ORA-06512: at line 2
 
The code from the Stored Procedure  DWH_CFG_POST_CONFIG_SP  which fails is the following:
 
-- Compile all INVALID objects
DBMS_UTILITY.COMPILE_SCHEMA(USER, FALSE);

Environment

Release: 15.5

Cause

Due to an Oracle issue, when it tries to compile objects across several schema at the same time.

Resolution

This issue is fixed as DE43056 in Clarity PPM 15.5.1.
 
Workaround: Re-run the job, remember to run Full Load immediately after a failure.