OBIA 11g: ORA-20000: Error Creating Index/Constraint : W_XACT_TYPE_D_U1 => ORA-01452 cannot CREATE UNIQUE INDEX; duplicate keys found (Doc ID 1629129.1)

Last updated on JANUARY 31, 2017

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.7.1 and later
Information in this document applies to any platform.

Symptoms

We are getting following error during the load:

ODI-1519: Serial step "Start Load Plan (InternalID:5290500)" failed because child step "Global Variable Refresh (InternalID:5291500)" is in error.
ODI-1519: Serial step "Global Variable Refresh (InternalID:5291500)" failed because child step "Warehouse Load Phase (InternalID:6287500)" is in error.
ODI-1519: Serial step "Warehouse Load Phase (InternalID:6287500)" failed because child step "1 SIL Load (InternalID:6288500)" is in error.
ODI-1519: Serial step "1 SIL Load (InternalID:6288500)" failed because child step "2 SIL Dimension Group (InternalID:6289500)" is in error.
ODI-1519: Serial step "2 SIL Dimension Group (InternalID:6289500)" failed because child step "Parallel - Others (InternalID:6355500)" is in error.
ODI-1518: Parallel step "Parallel - Others (InternalID:6355500)" failed; 2 child step(s) in error, which is more than the maximum number of allowed errors (0) defined for the parallel step. Failed child steps: 3 SIL Dims TRXTYPE_DIM (InternalID:6625500), 3 SIL Dims REASON_DIM (InternalID:6648500)
ODI-1519: Serial step "3 SIL Dims TRXTYPE_DIM (InternalID:6625500)" failed because child step "Finalize TRXTYPE_DIM (InternalID:6631500)" is in error.
ODI-1519: Serial step "Finalize TRXTYPE_DIM (InternalID:6631500)" failed because child step "EXEC_TABLE_MAINT_PROC (InternalID:6632500)" is in error.
ODI-1217: Session EXEC_TABLE_MAINT_PROC (4318500) fails with return code 20000.
ODI-1226: Step TABLE_MAINT_PROC fails after 1 attempt(s).
ODI-1232: Procedure TABLE_MAINT_PROC execution fails.
ODI-1228: Task TABLE_MAINT_PROC (Procedure) fails on the target ORACLE connection BIAPPS_DW.
Caused By: java.sql.SQLException: ORA-20000: Error creating Index/Constraint : W_XACT_TYPE_D_U1 => ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
ORA-06512: at line 142


Both TRX_TYPE_DIM, REASON_DIM same error: cannot CREATE UNIQUE INDEX; duplicate keys found

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