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 "ORA-00054: resource busy..." (Oracle)

book

Article ID: 104970

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

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

Intermittently, the Incremental Load DWH job may fail with the error. However, if a full Load DWH job is run, it usually completes with no issue. In some cases, if the full load fails, your DBA might have to make sure the session on the DWH database is killed before restarting the job from the UI.

Cause

DE36882 

Environment

Release: 15.2 and 15.3

Component: Load Data Warehouse 

Database: Oracle

Resolution

Fixed in 15.4

Workaround:

  1. Run the following SQL script on the DWH database:  

    BEGIN
    FOR J IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME NOT LIKE 'SYS%')
    LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '|| J.INDEX_NAME || ' NOLOGGING NOPARALLEL';
    END LOOP J;
    END;

  2. Also, disable parallel for the 3 tables that have the CLOB columns (has to also be run by DBA on the DWH database)
     
    alter table DWH_CFG_LOG noparallel
    alter table DWH_TRD_ERROR_MESSAGES noparallel
    alter table DWH_CMN_ERROR_MESSAGE noparallel

Additional Information

See also: