3 Replies Latest reply: May 7, 2012 12:52 PM by Ben Speckhard-Oracle RSS

    Unique Constraint

    Tyson Jouglet
      Hello,

      OWM_VERSION: 11.1.0.7.0
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

      I have a situation where somewhere along the line the primary key for a few records were thrown off. This is causing workspace manager to try and insert the T-520575 workspace records into LIVE when they are merged back together however this causes a unique constraint violation. Here is what the data looks like:
      PK       UNQ1    UNQ2    WORKSPACE
      ----------------------------------
      2095989  495685  152568  T-520575
       491685  495685  152568  LIVE
      2095990  495687  152569  T-520575
       491687  495687  152569  LIVE
      2096052  495689  152570  T-520575
       491689  495689  152570  LIVE
      I tried deleting the records out of the T-520575 workspace and refreshing the table using the following, but it still raises the unique constraint.
         delete from table 
          where PK in( 2095989, 2095990, 2096052);
      
         dbms_wm.refreshtable(
            workspace    => 'T-520575',
            table_id     => 'TABLE',
            where_clause => 'PK in ( 491685, 491687, 491689)',
            auto_commit  => FALSE
         );   
      I can see that the records I deleted have a WM_OPTYPE of 'D' but do not have a retiretime. How do I retire these records so that I can refresh them from LIVE? or is there a better solution to correct this issue?

      Edit
      I should also add that LIVE is the parent of this workspace T-520575

      Thanks,
      Tyson

      Edited by: Tyson Jouglet on Apr 27, 2012 8:34 AM
        • 1. Re: Unique Constraint
          Ben Speckhard-Oracle
          Hi Tyson,

          Are you sure there are no other rows that have a conflicting value for the unique constraint? What you have described should work. Since the rows have been deleted, they are no longer considered when evaluating the constraints when executing RefreshTable. You should be able to both merge and refresh the workspace after deleting the rows. Also, the row(s) with the 'D' wm_optype would not have a retiretime value, unless the row was reinserted. Only the 'I' or 'U' row(s) would have a retiretime value in this case.

          If there are not any other rows, then I would need a more complete description. Are there any continuously refreshed workspaces that have T-520575 as a parent workspace? Those workspaces would also have to be checked.

          Regards,
          Ben
          • 2. Re: Unique Constraint
            aschilling
            if you are sure, that none of the already mentioned possible reasons applies for your case you may wanna check, whether you're affected by Bug #12730297. we also had the case that a UC violation was raised although we were refreshing the deletion of objects.
            in our case we were running on oracle 10g, but IIRC the bug also was present in 11g (ben? :-))

            regards,

            Andreas
            • 3. Re: Unique Constraint
              Ben Speckhard-Oracle
              That bug could potentially be the reason for the unique constraint violation as it affects versions up to 11.2.0.2. However, I cannot say for sure without more details. Essentially, the without the fix for the bug, Workspace Manager checks the entire workspace for unique constrqaint violations, not just the rows being refreshed as specified by the where clause.

              Regards,
              Ben