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

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse job fails with error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
Schedule Load Data Warehouse - usually Incremental to run periodically on the database. Intermittently it might fail with error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
If a Full is run after that it usually completed with no issue.
In some cases if Full fails DBA might have to make sure the session on DWH database is killed before restarting the job from UI.

Cause

DE36882 Load Data Warehouse job fails with database lock ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Affecting 15.2,15.3
Fixed in 15.4
 

Environment

Release: CODFSS99000-15.3-PPM SAAS FedRAMP-Sandbox-Small Environment
Component:

Resolution

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, please disable parallel for the 3 tables that have the CLOB columns (has to also be run by DBA on DWH database)
 
alter table DWH_CFG_LOG noparallel
alter table DWH_TRD_ERROR_MESSAGES noparallel
alter table DWH_CMN_ERROR_MESSAGE noparallel