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

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3.8 to 12.1.3.9 [Release 12.1]
Information in this document applies to any platform.
MSCPDC
MSCPDCW
MSC_CL_COLLECTION
TASK_ODS_RES_REQ
TASK_RES_REQ

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 : <<create_temp_table_index>>
17-FEB 00:29:09 : <<SQL>> 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 : <<LOAD_RES_REQ>>

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms