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.
DE36882 Load Data Warehouse job fails with database lock ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Fixed in 15.4
1. Run the following SQL script on the DWH database:
FOR J IN (SELECT INDEX_NAME FROM USER_INDEXES WHERE INDEX_NAME NOT LIKE 'SYS%')
EXECUTE IMMEDIATE 'ALTER INDEX '|| J.INDEX_NAME || ' NOLOGGING NOPARALLEL';
END LOOP J;
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