OBIA 11g: 11.1.1.7.1 Error in Data Load: ORA-20000: Error creating Index/Constraint : W_PSFT_WEVT_PS_U1 => ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at line 142 (Doc ID 1615571.1)

Last updated on JANUARY 31, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Symptoms

Background

Using BI Application Configuration Manager and Functional Setup Manger,
OBIA ELT Failure with ODI
The Source system is PeopleSoft HCM9.1

Problem

Error when  loading the data for the  HCM offerings

Caused By: java.sql.SQLException: ORA-20000: Error creating Index/Constraint : W_PSFT_WEVT_PS_U1 => ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at line 142

Found 445 duplicated rows in the table W_PSFT_WEVT_PS, that is why that can't create the unique index W_PSFT_WEVT_PS_U1 .  

Tested

Modified the ETL  SDE_PSFT_PersistedStage_WorkforceEvent interface to enable "Distinct Rows" for all three interfaces, but it didn't resolve the problem .

select count(*), EMPLID,EMPL_RCD,DATASOURCE_NUM_ID,EFFECTIVE_FROM_DT,EFFSEQ,EVENT_TYPE from W_PSFT_WEVT_PS group by EMPLID,EMPL_RCD,DATASOURCE_NUM_ID,EFFECTIVE_FROM_DT,EFFSEQ,EVENT_TYPE having count(*) >1;

Environment

OBIA 11.1.1.7.1on 64-bit Linux

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