ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job fails with error "ORA-00054 resource busy..." on 14.x releases using Oracle

book

Article ID: 4993

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) job fails with an error "ORA-00054 resource busy and acquire with NOWAIT specified or timeout expired" 

Steps to Reproduce:

  1. Schedule an Incremental Load Data Warehouse job a few times per day 

Expected Result: The job to complete successfully every time

Actual Result: The Load Data Warehouse job fails intermittently (sometimes once per month) with error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Any Full or Incremental Load after this will also fail until the lock is removed

Cause

This is bug CLRT-81011 

Environment

CLARITY 14.2, 14.3,14.4 with Oracle as Database

Resolution

Fixed in 15.1

Workaround:

  1. Contact your DBA to manually remove the lock from the specified table
  2. Run the Load Data Warehouse job - Full Load 
  3. To avoid the locks from happening, ask your DBA to run the following script on the DWH database: 

    BEGIN
        FOR J IN (select INDEX_NAME from USER_INDEXES where INDEX_NAME like 'DWH%')
          LOOP
          EXECUTE IMMEDIATE 'ALTER INDEX '|| J.INDEX_NAME || ' NOLOGGING NOPARALLEL';
        END LOOP J;
    END;

Additional Information

Reference also: