Load DWH job fails with "ORA-00054: resource busy..." (Oracle)

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.

Environment

Release: 15.2 and 15.3

Component: Load Data Warehouse 

Database: Oracle

Cause

DE36882 

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:

• Load DWH job fails with errors "ORA-02049: timeout..." or "ORA-00054: resource busy..."
• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures