My Oracle Support Banner

MSCALTBL: Alter Temporary ODS Tables Ends In Error ORA-12801 (Doc ID 2884824.1)

Last updated on JULY 21, 2022

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.2.6.3 and later
Information in this document applies to any platform.

Symptoms

MSCALTBL: Alter Temporary ODS Tables is ending in error


LINE 70
01-JUL 11:15:53 : Alter index: ALTER INDEX MSC."DEMANDS_U1_ALD" LOGGING
01-JUL 11:15:53 : UNIQUE index creation on temporary table DEMANDS_ALD has completed with SUCCESS.
01-JUL 11:16:02 : Alter index: ALTER INDEX MSC."DEMANDS_N9_ALD" LOGGING
01-JUL 11:16:10 : WARNING
01-JUL 11:16:10 : Unique index violated - "DEMANDS_N2_ALD"
01-JUL 11:16:10 : ORA-12801: error signaled in parallel query server P003, instance exaal8npdr01-b8ziz1:ALTSCDB1 (1)
01-JUL 11:16:10 : ======= Below records violate the unique constraint =======
01-JUL 11:16:51 : PLAN_ID -1 / SR_INSTANCE_ID 5029 / ORGANIZATION_ID 232 / INVENTORY_ITEM_ID 595037 / USING_ASSEMBLY_ITEM_ID 595037 / USING_ASSEMBLY_DEMAND_DATE 31-DEC-10
01-JUL 11:16:51 : PLAN_ID -1 / SR_INSTANCE_ID 5029 / ORGANIZATION_ID 232 / INVENTORY_ITEM_ID 595041 / USING_ASSEMBLY_ITEM_ID 595041 / USING_ASSEMBLY_DEMAND_DATE 31-DEC-10

--- THIS CONTINUES UNTIL ... LINE 3934418

01-JUL 11:19:30 : PLAN_ID -1 / SR_INSTANCE_ID 5029 / ORGANIZATION_ID 672 / INVENTORY_ITEM_ID 1183737 / USING_ASSEMBLY_ITEM_ID 4171010 / USING_ASSEMBLY_DEMAND_DATE 05-MAY-23
01-JUL 11:19:30 : PLAN_ID -1 / SR_INSTANCE_ID 5029 / ORGANIZATION_ID 672 / INVENTORY_ITEM_ID 1496408 / USING_ASSEMBLY_ITEM_ID 4171010 / USING_ASSEMBLY_DEMAND_DATE 05-MAY-23
01-JUL 11:19:30 : ===========================================================
01-JUL 11:19:30 : The above records would not be collected
01-JUL 11:19:30 : deleting the duplicate row DELETE FROM DEMANDS_ALD t1 WHERE EXISTS (SELECT 1 FROM DEMANDS_ALD t2 WHERE nvl( t1.PLAN_ID,'-99999') = nvl(t2.PLAN_ID,'-99999') AND nvl( t1.SR_INSTANCE_ID,'-99999') = nvl(t2.SR_INSTANCE_ID,'-99999') AND nvl( t1.ORGANIZATION_ID,'-99999') = nvl(t2.ORGANIZATION_ID,'-99999') AND nvl( t1.INVENTORY_ITEM_ID,'-99999') = nvl(t2.INVENTORY_ITEM_ID,'-99999') AND nvl( t1.USING_ASSEMBLY_ITEM_ID,'-99999') = nvl(t2.USING_ASSEMBLY_ITEM_ID,'-99999') AND nvl( t1.USING_ASSEMBLY_DEMAND_DATE,'-99999') = nvl(t2.USING_ASSEMBLY_DEMAND_DATE,'-99999') AND t1.rowid < t2.rowid)
01-JUL 11:27:09 : <>
01-JUL 11:27:09 : <> CREATE INDEX "DEMANDS_N2_ALD" ON DEMANDS_ALD(PLAN_ID,SR_INSTANCE_ID,ORGANIZATION_ID,INVENTORY_ITEM_ID,USING_ASSEMBLY_ITEM_ID,USING_ASSEMBLY_DEMAND_DATE) PARALLEL TABLESPACE APPS_TS_TX_IDX STORAGE( INITIAL 65536) PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
01-JUL 11:27:09 : ORA-12801: error signaled in parallel query server P000, instance exaal8npdr01-b8ziz1:ALTSCDB1 (1)
01-JUL 11:27:09 : NONUNIQUE index creation on temporary table DEMANDS_ALD has completed with ERROR.
NOTE : Latest trace and log do NOT show any ORA-00904 error

 

STEPS:
ASCP(R)  / Collections / Oracle Systems / Standard Data Collections
Launch the request set.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


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