Load DWH fails with "ORA-04022: nowait requested but had to wait to lock dictionary object"

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse (DWH) fails with the below error:

ERROR 2020-02-20 04:09:57,930 [Thread-419019] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :

Couldn't execute SQL: BEGIN

    DWH_CFG_POP_HISTORY_SP;

    EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE';

          DWH_CFG_PRE_CONFIG_SP(P_DBLINK => 'PPMDBLINK', P_FULL_RELOAD => 'Y');

          DWH_CFG_RECOMPILE_SP;

END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-04022: nowait requested, but had to wait to lock dictionary object

ORA-06512: at "PPM_DWH.CMN_DISABLE_CONSTRAINTS_SP", line 15

ORA-06512: at "PPM_DWH.DWH_CFG_PRE_CONFIG_SP", line 18

ORA-06512: at line 4

Environment

Release : All Supported

Component :DATA WAREHOUSE

Cause

Full load truncates all the tables of the DWH except for a few configuration tables. This issue occurs when there is an exclusive lock held by a Database (DB) session on one of the DWH tables which the job is trying to truncate.

The error essentially means that the DWH job tried to acquire a lock to truncate a table and it was unable to do so.

Resolution

1. Ask your DBA to run the attached query to identify the Locked tables along with the session details.
2. Once the session holding the lock is identified, request your DBA to kill it.
3. Re-run the DWH Full load job.
   
   Note: the Attached query needs DBA privileges to run.

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures