Incremental Load Data Warehouse Job Fails With Error ORA-02049

Products

• Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse incremental job fails with following error:

ERROR 2023-07-18 21:01:45,147 [Thread-56300] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry : 
Couldn't execute SQL: DELETE FROM DWH_CMN_PERIOD 
WHERE 
 NOT (
 EXISTS (
 SELECT 1
 FROM 
 [email protected] SRC
 WHERE 
 SRC.PERIOD_KEY = DWH_CMN_PERIOD.PERIOD_KEY
 )
 )

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02049: timeout: distributed transaction waiting for lock

 

Environment

Release: All

Resolution

The solution is to:

1. Run command: sho parameter distributed_lock_timeout

If the value is 60, then the value should be increased to 600 seconds

2. Increase the SHARED_POOL_SIZE value in init.ora

3. Flush the shared pool with ALTER SYSTEM FLUSH SHARED_POOL