Load DWH job fails intermittently on the incremental run on DWH_INV_PER_FACTS_LOAD (DE38650) ORA-01452: cannot CREATE UNIQUE INDEX

book

Article ID: 124885

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

Why does this job fail intermittently?

An error occurred executing this job entry : 2019/01/15 14:43:24 - ClarityDB - isOracle? - Couldn't execute SQL: BEGIN 2019/01/15 14:43:24 - ClarityDB - isOracle? - DWH_INV_PER_FACTS_LOAD( 2019/01/15 14:43:24 - ClarityDB - isOracle? - P_DBLINK => 'SCHEMA', 2019/01/15 14:43:24 - ClarityDB - isOracle? - P_ARRAY_SIZE => 50000 2019/01/15 14:43:24 - ClarityDB - isOracle? - ); 2019/01/15 14:43:24 - ClarityDB - isOracle? - END; 2019/01/15 14:43:24 - ClarityDB - isOracle? - 2019/01/15 14:43:24 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_PER_FACTS_LOAD. SQLERRM : ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INTERNAL_MD. SQLERRM : ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found 2019/01/15 14:43:24 - ClarityDB - isOracle? - ORA-06512: at "PPM_DWH.DWH_INV_PER_FACTS_LOAD", line 75 2019/01/15 14:43:24 - ClarityDB - isOracle? - ORA-06512: at line 2

Cause

This is reported as DE38650, which is fixed in 15.5.0+

Environment

Release:
Component: PPMDWH

Resolution

1. Upgrade to Clarity 15.5.0+ or
2. Run the Load DWH job until upgrade can take place.