Planning ODS Load Worker Fails with ORA-00054 Creating Indexes on Temp Resource Requirements (Doc ID 1669619.1)

Last updated on AUGUST 29, 2023

Applies to:

Symptoms

Using Advanced Supply Chain Planning / Standard Data Collection
On VCP 12.1.3.8 through VCP 12.1.3.9.1,
When attempting to perform Planning ODS Load, the following error occurs.

ERROR
-----------------------
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
UNIQUE index creation on temporary table RESOURCE_REQUIREMENTS_xxx has completed with ERROR.

-- Steps To Reproduce:

Customer states the issue can be reproduced at will at customer site with the following steps:
1. (R) Advanced Supply Chain Planner
2. (N) Collections / Oracle Systems / Standard Collection
3. Observe the noted error in log file

example:

17-FEB 00:29:09 : MSC_CL_EXCHANGE_PARTTBL.Initialize successful
P_table_name = MSC_RESOURCE_REQUIREMENTS
17-FEB 00:29:09 : <>
17-FEB 00:29:09 : <> CREATE UNIQUE INDEX RESOURCE_REQUIREMENTS_U1_APP ON RESOURCE_REQUIREMENTS_APP(PLAN_ID,TRANSACTION_ID,SR_INSTANCE_ID) PARALLEL 3
TABLESPACE APPS_TS_TX_IDX STORAGE(  INITIAL 131072) PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
17-FEB 00:29:09 : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
17-FEB 00:29:09 : UNIQUE index creation on temporary table RESOURCE_REQUIREMENTS_APP has completed with ERROR.
17-FEB 00:29:09 : <>

Also note that sometimes  this ORA-00054 error is NOT be in the log file and only see in Trace file.
example:
LOG FILE

22-AUG 07:04:44 : NONUNIQUE index creation on temporary table RESOURCE_REQUIREMENTS_RBC has completed with ERROR.
22-AUG 07:04:44 : User-Defined Exception
22-AUG 07:04:44 : User-Defined Exception
22-AUG 07:04:44 : Sending message :
            -9999999 to monitor.
22-AUG 07:04:44 : Error_Stack...
22-AUG 07:04:44 : ORA-06510: PL/SQL: unhandled user-defined exception

22-AUG 07:04:44 : Error_Backtrace...
22-AUG 07:04:44 : ORA-06512: at "APPS.MSC_CL_COLLECTION", line 2340
ORA-06512: at "APPS.MSC_CL_COLLECTION", line 3832

TKPROF FILE
The following statement encountered a error during parse:
CREATE INDEX  ....... STATISTICS
Error encountered: ORA-00054

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.