This discussion is archived
5 Replies Latest reply: Oct 15, 2009 5:39 AM by 670459 RSS

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

415769 Newbie
Currently Being Moderated
Hi,
The tasks
TASK_GROUP_Extract_EmployeeDimension
Load into Position Dimension

getting the following error

484 SEVERE Sun Jun 22 11:55:55 PDT 2008 Failure detected while executing CREATE INDEX:W_EMPLOYEE_DS:W_EMPLOYEE_DS_U1.
Error Code: 12801.
Error Message: Error while execution : CREATE UNIQUE INDEX
     W_EMPLOYEE_DS_U1
ON
     W_EMPLOYEE_DS
(
     INTEGRATION_ID ASC
     ,DATASOURCE_NUM_ID ASC
     ,SRC_EFF_FROM_DT ASC
)
NOLOGGING PARALLEL TABLESPACE qbiabidwidx
with error java.sql.SQLException: ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Any suggestion

Thanks
Ganesan
  • 1. Re: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    199151 Explorer
    Currently Being Moderated
    I have posted about this error before on this forum.

    It is related to two HR event entires for a person in the Vision DB.

    Look at this posting:

    Title: Unique Index Error while running the ETL process, Posted: Feb 7, 2008 11:49 AM

    You can search by that title.
  • 2. Re: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    Nathan John Morgan Journeyer
    Currently Being Moderated
    In 11510,

    The duplicate row loaded is person 6272 with effective date 1994 and 1991. This needs to be fixed in vision.

    Workflow SIL_PositionDimension_Full calls mapping SIL_PositionDimension to load target W_POSITION_D from source W_POSITION_DS. W_POSITION_D.INTEGRATION_ID maps to W_POSITION_DS.INTEGRATION_ID, but EFFECTIVE_FROM_DT is a more complicated many to one mapping.

    Extract to W_POSITION_DS ;

    SDE_ORA_PositionDimension_NonEmployee
    - W_POSITION_DS.INTEGRATION_ID from PER_ALL_PEOPLE_F.PERSON_ID

    SDE_ORA_PositionDimension_NoAssignments
    - W_POSITION_DS.INTEGRATION_ID from JTF_RS_RESOURCE_EXTNS_TL RESOURCE_ID

    -- PER~6272,4,01-JAN-94
    -- PER~6272,4,01-JAN-91
    select all t.*
    from
    (
    select all
    count(*) as n
    , t.INTEGRATION_ID
    , t.DATASOURCE_NUM_ID
    , t.EFFECTIVE_FROM_DT
    --, t.*
    from baw.W_POSITION_D t
    group by
    null
    , t.INTEGRATION_ID
    , t.DATASOURCE_NUM_ID
    , t.EFFECTIVE_FROM_DT
    ) t
    where ( ( n > 1 ) )
    ;
  • 3. Re: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    remc0 Journeyer
    Currently Being Moderated
    Hehe this is a bug in the vision database indeed...

    if you look in the per_all_people_f table you will find the following:

    PERSON_ID----------------EFFECTIVE_START_DATE------------EFFECTIVE_END_DATE
    6272-------------------------4-jan-91--------------------------------6-feb-02
    6272-------------------------4-jan-91--------------------------------31-dec-12

    Just alter the effective_start_date of the second row to 7-feb-02 and it works again!!

    Normally this can't be done in APPS, because this has to do with datetrack. There can only be 1 active record in time, here you got 2 active records!!

    Edited by: Remc0 on 21-apr-2009 10:29
  • 4. Re: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    716953 Newbie
    Currently Being Moderated
    This was very helpful. I ran into the same problem. And i was able to fix by fixing the date.

    Thanks alot
  • 5. Re: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    670459 Newbie
    Currently Being Moderated
    select user_role_view from wf_directory_partitions where user_role_view like '%POS%';

    if any of the rows returned

    then take a backup of the above table and delete the rows that were returned.

    then restart the concurrent managers, kick of Sync_Wf_local_tables it should work fine with out the duplicate primary key issue/index issue

    Hitendra.