This discussion is archived
13 Replies Latest reply: Jan 10, 2011 6:32 PM by Ben Speckhard RSS

False Unique Constraint Violation on RefreshTable

aschilling Newbie
Currently Being Moderated
hi!

we're again having a serious issue.
since some days any refreshes on a certain table fail with a UC violation that definitely is wrong and we wonder how that happens.
this is the output (probably doesn't help too much, except maybe for the line numer in SYS.LT?):
ORA-20232: unique constraint (CO2MO.FZG_KOMBINATION_UK) violated
ORA-06512: in "SYS.LT", Zeile 7139
ORA-06512: in Zeile 1
the violation occurs for both new objects as well as updated objects, it does not occur on MergeTable.
EDIT: it also does not occur for all workspaces. we have the following hierarchy (top-down):
LIVE
W1 W2
W1_1, W1_2, W2_1
while refreshing from W1 to W1_1 or W1_2 does not work, it does work from LIVE to W2 and from W2 to W2_1.
also, we made a test that we deleted the whole content of the table in one of the workspaces that caused trouble and then it also worked.
[ end EDIT]

we already tried enabling/disabling the constraint and also dropping/readding it, nothing helped.
any ideas what might be the problem? the change after which the problem occured first (probably; we're not 100% sure here) was that we modified a FK of the table we're having the problem with to another table and set that FK to be CASCADE DELETE.
we also traced the call, though we couldn't find anything suspicious this time :-/ any hints what we can look for in the trace?

any help is appreciated.

regards,

Andreas
  • 1. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Andreas,

    This error is going to be dependent on the actual data, so the only thing a trace file would provide would be the SQL statement that identified the rows in the table that are causing the violation. This is most likely the last query on the base _LT table prior to the error being raised.

    What parameter options did you use for dbms_wm.RefreshWorkspace? With the default copy_data parameter set to false, all of the savepoints within the child workspace are also checked for unique constraint violations. When this parameter is set to true, only the LATEST data is checked since only that savepoint is being modified by the operation.

    If the violation is occurring at a previous savepoint, you could choose to either use copy_data=>true or you could rollback the changes to the particular row causing the violation using dbms_wm.RollbackTable.

    Regards,
    Ben
  • 2. Re: False Unique Constraint Violation on RefreshTable
    aschilling Newbie
    Currently Being Moderated
    Hi Ben,

    we aren't using Refresh/MergeWorkspace, we always use RefreshTable and MergeTable due to the fact that our users need to be able to decide for every table which objects to refresh/merge.
    So, the *Table methods don't have the copy_data parameter.
    Also, when the error occured the first time the object (let's call it A) had a FK relation to an object B that also was new at that moment (and was also refreshed). Thus it was 100% guaranteed that A could not have existed at any point in time before, because the FK relation to B is in the Unique Constraint.
    We did a SELECT on the LT table with all fields of the Unique Constraint and we got exactly one row returned (the row we tried to refresh into another workspace). If there was any other object with the same Unique Constraint I assume the SELECT should have returned at least two rows. Apart from that we have a generic UC check in our application that uses the USERWM_CONS_COLUMNS view to check for UC violations in the target workspace. It didn't show us a violation for the object.

    regards,

    Andreas
  • 3. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    In that case, I would suggest to file a SR. As I mentioned, it is very dependent on the data, so not much I can say without being able to reproduce it or at least see the full trace. Does the workspace being refreshed have any continually refreshed child workspaces?

    Regards,
    Ben
  • 4. Re: False Unique Constraint Violation on RefreshTable
    aschilling Newbie
    Currently Being Moderated
    hi Ben!

    no, we don't have any continually refreshed workspaces.
    when you're saying it is dependent on the data: is it possible that we're encountering that problem allthough the _LT table has only one row with that UC? what I'm asking is: can something like that happen although there is no bug and although everything looks as if it can't happen? (except for the cases you already mentioned, which unfortunately do not apply for us).
    btw, as a test we disabled the constraint (inside BeginDDL()/CommitDDL()) and got another error, similar to the one we already had some weeks ago:
    Caused by: java.sql.SQLException: ORA-00936: missing expression
    ORA-06512: in "SYS.LT", Zeile 7139
    ORA-06512: in "CO2MO.C2_WM", Zeile 247
    ORA-06512: in Zeile 1
    shouldn't it actually work with disabled constraint?
    trace for that case:
    The following statements encountered a error during parse:
    
    select 1 from dual where exists (
               select 1 from
               (SELECT /*+ INDEX(C2_FAHRZEUGE_LT FZG_PK) */ FZG_ID, FROM CO2MO.C2_FAHRZEUGE_LT t1
                WHERE (version in (select version from wmsys.wm$modified_tables
              where workspace = 'Mad Onion Test' and
                         table_name = 'CO2MO.C2_FAHRZEUGE' and
                         version > 375 )  and
           (nextver = '-1' or
            (not exists (select 1 from wmsys.wm$nextver_table
                         where version in (select version from wmsys.wm$modified_tables
              where workspace = 'Mad Onion Test' and
                         table_name = 'CO2MO.C2_FAHRZEUGE' and
                         version > 375 )  and
                               nextver = next_vers)
           )) and
           delstatus > 0 )) v
                where (
                 (wm$col1 is null or wm$col2 is null or wm$col3 is null)
                 and not (wm$col1 is null and wm$col2 is null and wm$col3 is null)
                 )
               )
    problem I see on a first glance:
    SELECT /*+ INDEX(C2_FAHRZEUGE_LT FZG_PK) */ FZG_ID, FROM CO2MO.C2_FAHRZEUGE_LT
    there seems to be something missing before the from

    maybe this helps to find out what could be wrong? but maybe it's also expected that it does not work with disabled constraint, though that would be surprising to us.

    regards,

    Andreas
  • 5. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    A disabled constraint should not cause a unique key violation during a refresh. What version of OWM are you using? This looks like a bug that has already been fixed.

    Regards,
    Ben
  • 6. Re: False Unique Constraint Violation on RefreshTable
    aschilling Newbie
    Currently Being Moderated
    Hi Ben,

    our OWM version is 10.2.0.1.0
    The customer instance (the problem didn't occur there yet as we did't run our DDL-script there so far after facing the problem on our development instance) is running 10.2.0.4.3
    Are these versions known to have a bug that might cause this?

    regards,

    Andreas
  • 7. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Both of those versions will have a bug related to disabled constraints. Dropping the constraint, instead of disabling it, would work as expected.

    The unique key violation error would be distinct from this however. It might be worth trying an upgrade to see if that fixes the problem. Without knowing what the problem is, I can't say whether it would or not. If you have a trace for this, you could file a SR, which would be able to provide a more definitive answer as to what is happening.

    Regards,
    Ben
  • 8. Re: False Unique Constraint Violation on RefreshTable
    aschilling Newbie
    Currently Being Moderated
    hi Ben,

    we also thought about trying an upgrade, will give it a try.
    which is the one to choose? we have to admit we're somewhat lost in the oracle supprt site as the list of patches is quite huge.
    there isn't a patch for the OWM directly but also among the RDBMS Server patches we find none that might be fitting (for the 10.2.0.1 version that is)

    regards,

    Andreas
  • 9. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    Workspace Manager patches are only included with the database patchsets as of 10.2.0.4, which is OWM version 10.2.0.4.3.

    You also have the option to install OWM 10.2.0.4.4 on any database version up to and including 10.2.0.3. This OWM patch is #7341988.

    Regards,
    Ben
  • 10. Re: False Unique Constraint Violation on RefreshTable
    aschilling Newbie
    Currently Being Moderated
    hi Ben,

    yesterday we were facing this problem again, this time on the production system of our customer.
    it was reproducable for one table and only occurred during refresh, not during merge.
    today we wanted to ask their DBA to enable tracing but it appeared that this morning they could actually perform the synchronization and the error did not appear again.
    the only thing that happened in the meantime was, that their DBA migrated the production system data to the test system (basically by importing a backup) and thus the test instance
    was restarted during that process. however the production system never went offline but in some way it seemed to be affected (to the good this time ;))
    is it possible that the error appears due to something like caching? and maybe some cache was emptied after the test instance was restarted?

    kind regards,

    Andreas Schilling
  • 11. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Andreas,

    If you able to get a trace of a session where the procedure is failing, there would be a SQL statement based on the base _LT table(s) just prior to the ORA-20232 being raised that should provide a much better idea as to why this error is occurring.  If you could provide that (or raise an SR and provide the actual trace), I can take a closer look.

    Also, any caching errors wouldn't be directly Workspace Manager related, and so would need to be looked at by someone in the database group. I don't have as much experience with those situations.

    Regards,
    Ben
  • 12. Re: False Unique Constraint Violation on RefreshTable
    aschilling Newbie
    Currently Being Moderated
    hi Ben!

    with the help of the DBA we got a trace. however, for some odd reason the UC violation is not in there, which is something I do not understand.
    nevertheless I can provide you the output of the trace (done with tkprof): output
    maybe you still can see something in there?
    the first statement of the trace (refreshtable) is what finally causes the violation and the constraint is WER_KOMBINATION_UK (which only occures once in the file).
    if that does not help you to find something: do you have any hint on how to get a trace that contains the constrain violation? we also did it again with system wide tracing enabled which produced a bigger trace file but it still didn't contain any errors.
    the only constant with that problem is, that the error occurs on a refresh. the table it occurs on and the workspace can change.

    kind regards,

    Andreas
  • 13. Re: False Unique Constraint Violation on RefreshTable
    Ben Speckhard Pro
    Currently Being Moderated
    Hi,

    I'd recommend filing a SR with all the traces that you do have. If it doesn't end up being an OWM problem(for example, caching as you mentioned), it could be seen by the appropriate group. Not much I can go on without the trace containing the error.

    Ben

Legend

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