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

Document ID : KB000104970
Last Modified Date : 05/07/2018
Show Technical Document Details
Issue:
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
 
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