Hello fellow communities members,
For your information, we have now fixed the following Clarity PPM issue:
CLRT-81011 Load Data Warehouse job fails with database lock ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Summary:
The Load Data Warehouse job fails intermittently (sometimes once per month) with error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. Any Full or Incremental Load after this will also fail until the database lock is removed
This is fixed in 15.2 (not a released version yet) and will also be ported to some of our lower version patches.
In the meantime, this is a workaround (for On Premise customers):
1. Contact the DBA to manually remove the lock from the specified table, then run Load Data Warehouse job - Full Load
2. To avoid the locks from happening in future ask the DBA to run the following script on the DWH database:
BEGIN
FOR J IN (select INDEX_NAME from USER_INDEXES where INDEX_NAME like 'DWH%')
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX '|| J.INDEX_NAME || ' NOLOGGING NOPARALLEL';
END LOOP J;
END;
This will disable the parallel index rebuild, which was contributing to the issue.
I hope this helps -Nika