This discussion is archived
3 Replies Latest reply: May 7, 2012 10:52 AM by Ben Speckhard RSS

Unique Constraint

Tyson Jouglet Expert
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points