Refresh Collection Snapshots OR Planning Data Pull Worker Errors With ORA-01502 on Index INV.MTL_DEMAND_SN_N1 (Doc ID 459415.1)

Last updated on APRIL 29, 2024

Applies to:

Symptoms

Refresh Collection Snapshots - MSRFWOR - program has an error with the following message

ORA-12008: error in materialized view refresh path
and
ORA-01502: index INV.MTL_SUPPLY_SN_N1 or partition of such index is in unusable state"

Log file shows:
MSRFWOR module: Refresh Collection Snapshots
+---------------------------------------------------------------------------+

Current system time is 11-FEB-2008 09:22:53

+---------------------------------------------------------------------------+

**Starts**11-FEB-2008 09:22:55
**Ends**11-FEB-2008 09:22:58
ORA-12008: error in materialized view refresh path
ORA-01502: index 'INV.MTL_SUPPLY_SN_N1' or partition of such index is in unusable state
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
The degree of parallelism for Refreshing snapshots is set to:  0
ORA-12008: error in materialized view refresh path
ORA-01502: index 'INV.MTL_SUPPLY_SN_N1' or partition of such index is in unusable state

 OR

MSCPDPW: Planning Data Pull Worker
.....
24-JUL 10:34:21 : Task Number is 16
24-JUL 10:34:21 : Global RN (msc_cl_pull.v_lrn): -1
24-JUL 10:34:21 : Procedure MSC_CL_WIP_PULL.LOAD_WIP_DEMAND started.
24-JUL 10:34:22 : Error pulling data from MRP_AP_WIP_COMP_DEMANDS_V to MSC_ST_DEMANDS.
24-JUL 10:34:22 : ORA-01502: index 'APPS.MTL_SYS_ITEMS_SN_N2' or partition of such index is in unusable state
ORA-02063: preceding line from <ABC_TO_XYZ>
24-JUL 10:34:22 : ORA-01502: index 'APPS.MTL_SYS_ITEMS_SN_N2' or partition of such index is in unusable state
ORA-02063: preceding line from <ABC_TO_XYZ>
24-JUL 10:34:22 : Error_Stack...
24-JUL 10:34:22 : ORA-01502: index 'APPS.MTL_SYS_ITEMS_SN_N2' or partition of such index is in unusable state
ORA-02063: preceding line from <ABC_TO_XYZ>

24-JUL 10:34:22 : Error_Backtrace...
24-JUL 10:34:22 : ORA-06512: at "APPS.MSC_CL_PULL", line 7060
ORA-06512: at "APPS.MSC_CL_PULL", line 1808

Cause

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