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

Article Id: 104970

Status: Published

Updated On: 05-07-2018 08:37

Products:

CLARITY PPM FOR ITG , Clarity PPM On Premise , CLARITY PPM FEDERAL , Clarity PPM SaaS , Clarity PPM SaaS - Application , Clarity PPM SaaS - Application , 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