2 Replies Latest reply on Mar 23, 2009 4:17 PM by Christian Berg

    Create unique index error

    662126
      Hi All,

      While running the ETL process from DAC for Execution Plan 'Supply Chain Oracle 11.5.10' one of the tasks is going to status Failed.

      When I checked the log files for this tasks, SDE_ORA_UserDimension_Full I found the following error

      ANOMALY INFO::: Error while executing : CREATE INDEX:W_USER_DS:W_USER_DS_U1
      MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX
           W_USER_DS_U1
      ON
           W_USER_DS
      (
           INTEGRATION_ID ASC
           ,DATASOURCE_NUM_ID ASC
           ,SRC_EFF_FROM_DT ASC
      )
      NOLOGGING PARALLEL
      with error java.sql.SQLException: ORA-12801: error signaled in parallel query server P000
      ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

      I checked the source tables FND_USER and PER_ALL_PEOPLE . They had no duplicate entries.
      The column in the target table W_USER_DS thats non-unique is the INTEGRATION_ID which is not from the source tables but i guess is created during ETL.
      How do i prevent creation of duplicate INTEGRATION_ID? Or is the source of the problem not this?
      I have been trying to figure out a solution for past 1 week. Kindly share your views .


      Thanks,
      Ragav
        • 1. Re: Create unique index error
          634114
          Hey Ragav,
          I too faced same issue during my initial days of Bi Apps! Even though we don't have duplicate records for those source tables, we are getting duplicte records while loading!! So, here I have 2 options: Either not to create unique indexes while loading or delete that record which is causing duplicate records while loading. Even Oracle told me to remove that record which is causing the duplication.
          SO, at this moment, if you are building your system for demo purpose, then have your ETL run by removing Unique index.
          Good Luck
          • 2. Re: Create unique index error
            Christian Berg
            INTEGRATION_ID is normally the ID column from the source system. I.e.: it MUST NOT be a non-unique column!

            Check your source qualifier and / or SQL statement to make sure that you get unique rows into the dimension staging table!

            Cheers,
            C.