5 Replies Latest reply: Oct 15, 2009 7:39 AM by 670459 RSS

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

    415769
      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
          Damon A. Runion
          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
            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
              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
                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
                  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.