Load Data Warehouse job fails with error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired (Oracle)
book
Article ID: 104970
calendar_today
Updated On:
Products
CLARITY PPM FOR ITGCLARITY PPM FEDERALClarity PPM SaaSClarity 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
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