MSCPDC - ODS Load Fails with Errors: ORA-00942, ORA-14098 index mismatch for tables in ALTER TABLE EXCHANGE PARTITION (Doc ID 340118.1)

Last updated on SEPTEMBER 26, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10 and later
Information in this document applies to any platform.
MSCPDC - Planning ODS Load

Symptoms

 

In 11.5.10

<>ALTER TABLE MSC_ITEM_CATEGORIES EXCHANGE PARTITION
ITEM_CATEGORIES__1 WITH TABLE ITEM_CATEGORIES_CRP INCLUDING INDEXES
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

In R12.1

04-JUN 10:33:17 : successfully altered phase 2 tables.
04-JUN 10:33:17 : exchange part:-ALTER TABLE MSC_SYSTEM_ITEMS EXCHANGE PARTITION SYSTEM_ITEMS__1 WITH TABLE SYSTEM_ITEMS_PRD INCLUDING INDEXES
04-JUN 10:33:22 : exchange part:-ALTER TABLE MSC_RESOURCE_REQUIREMENTS EXCHANGE PARTITION RESOURCE_REQUIREMENTS__1 WITH TABLE RESOURCE_REQUIREMENTS_PRD INCLUDING INDEXES
04-JUN 10:33:22 : ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION  <<< This is the KEY ERROR to resolve <<<<<
04-JUN 10:33:22 : Fatal Error in Exchange Partition.
04-JUN 10:33:35 :  Calling Linking of Sales Order for 11i source ...
04-JUN 10:33:35 : Procedure MSC_CL_DEMAND_ODS_LOAD.LINK_SUPP_SO_DEMAND_EXT started.
04-JUN 10:36:41 : .. In procedure GET_START_END_OP ..
04-JUN 10:36:47 : Truncated Source AD Tables Successfully
04-JUN 10:36:47 : exchange part:-ALTER TABLE MSC_ST_DELIVERY_DETAILS EXCHANGE PARTITION DELIVERY_DETAILS_1 WITH TABLE DELIVERY_DETAILS_PRD  
04-JUN 10:36:47 : ORA-00942: table or view does not exist  <<<<< IGNORE THIS ERROR This happens because of the ORA-14098 reported above
04-JUN 10:36:47 :  Error while exchanging partition for table :MSC_ST_DELIVERY_DETAILS
04-JUN 10:36:47 :  Please launch planning data pull again for following entities :
04-JUN 10:36:47 : Delivery Details
04-JUN 10:36:47 : Sourcing Rules
04-JUN 10:36:47 : Sourcing Rules
04-JUN 10:36:47 : Sourcing Rules
04-JUN 10:36:47 :  Exchange partition failed
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+


UPDATE 26-SEP-2017 NEW SYMPTOM - ORA-00911 can occur on a table

P_table_name = MSC_DEMANDS (OR OTHER TABLE)
12-SEP 08:26:27 : UNIQUE index creation on temporary table DEMANDS_PRD has completed with SUCCESS.
P_table_name = MSC_DEMANDS
12-SEP 08:26:27 : <<create_temp_table_index>>
12-SEP 08:26:27 : <<SQL>> CREATE INDEX DEMANDS~N12_PRD ON DEMANDS_PRD(PLAN_ID,ORDER_NUMBER#1,ORIGINATION_TYPE) PARALLEL TABLESPACE APPS_TS_TX_IDX PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
12-SEP 08:26:27 : ORA-00911: invalid character
12-SEP 08:26:27 : NONUNIQUE index creation on temporary table DEMANDS_PRD has completed with ERROR.

 
  

Note: Do not concentrate on ORA-923 or ORA-942 when ORA-14098 is also present.
Fix ORA-14098 using this note, then the other errors will be resolved.
AND -
Note that you may fix one table and have another table throw the same ORA-14098 error. We have seen cases where this error happens for different tables sequentially.
  

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