This discussion is archived
6 Replies Latest reply: Aug 24, 2011 12:31 PM by Ben Speckhard RSS

ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts

882731 Newbie
Currently Being Moderated
We are getting unique constraint error in Production Database. We are able to merge most of the workspaces with no issues. One workspace is givign errors on merge.
Here is the code

begin
dbms_wm.gotoworkspace('denialletterspoc_onmzpulp');
dbms_wm.setdiffversions('denialletterspoc_onmzpulp','LIVE');
dbms_wm.SetConflictWorkspace('denialletterspoc_onmzpulp');
commit;
DBMS_WM.BeginResolve('denialletterspoc_onmzpulp');
DBMS_WM.ResolveConflicts(
workspace => 'denialletterspoc_onmzpulp',
table_name => 'MCM_MESSAGE',
where_clause => ' id in (8403026,10134822,8259485,8259488,8426289,8426287,10186643,8426288) ',
keep => 'CHILD'
);
exception when others then
DBMS_WM.RollbackResolve('denialletterspoc_onmzpulp');
raise;
end;

ORA-00001: unique constraint (T2.MCM_MESSAGE_PK) violated
ORA-06512: at line 16

I looked at the date in XXX_lt tables and it does not look suspecious. Please help us understand
ORA-00001 and ORA-06512 error.
Amit Gangwar
  • 1. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Amit,

    What version of Workspace Manager are you using?

    It's hard to say what could be causing the unique constraint violation without seeing the table data. I would recommend to run ResolveConflicts separately on each of the primary key values listed in the where_clause. The error should still occur for the one that is responsible. You could then query _LT based on that value which should help in determining the cause.

    Regards,
    Ben
  • 2. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
    883548 Newbie
    Currently Being Moderated
    Hi, Ben

    Our OWM_VERSION is 10.2.0.4.3.

    I tried disable and enable constraint MCM_MESSAGE_PK. But didn't fix our issue.

    Split where clause from " id in (...) " syntax to " id = ... " syntax will reduce performance heavily. And I did some test that " id in (...) " syntax works well in our dev env.

    I opened our trace file and get below plsql block. When I perform it get same error with our original codes. Please notice the part of "forall wm_indx in WM_rowids.first .. WM_rowids.last execute immediate 'begin insert into T2.MCM_MESSAGE_lt ... ...", error occurred here. But if I change the "forall" syntax to normal " for ... loop" syntax then no error here. Can you give me some advice or suggestion?

    DECLARE
    WM_rowids wmsys.lt_ctx_pkg.rowidTabType;
    WM_rowids_vt wmsys.lt_ctx_pkg.rowidTabType;
    WM_curver wmsys.lt_ctx_pkg.int_tab;
    WM_vtRow wmsys.lt_ctx_pkg.int_tab;
    WM_nextver wmsys.lt_ctx_pkg.nextver_tab;
    WM_found integer := 0;

    BEGIN
    dbms_wm.gotoworkspace('denialletterspoc_onmzpulp');
    dbms_wm.setdiffversions('denialletterspoc_onmzpulp','LIVE');
    dbms_wm.SetConflictWorkspace('denialletterspoc_onmzpulp');
    /* update the auxilliary table of snapshots */
    /* These are rows in the current version that have already been resolved once */
    update T2.MCM_MESSAGE_aux st
    set (snapShotChild, snapShotParent, versionParent) =
    (select p.WM_childds, p.WM_parentds, p.WM_parentver
    from T2.MCM_MESSAGE_pkc p
    where p.ID = st.ID),
    value = '0',
    wm_opcode = 'CC'
    where st.versionChild = 614
    and (ID) in (select ID
    from T2.MCM_MESSAGE_pkc p
    where (id in (8403026,
    10134822,
    8259485,
    8259488,
    8426289,
    8426287,
    10186643,
    8426288)));
    select distinct WM_ridchild bulk collect
    into WM_rowids
    from T2.MCM_MESSAGE_pkc
    where (id in (8403026,
    10134822,
    8259485,
    8259488,
    8426289,
    8426287,
    10186643,
    8426288))
    and WM_childver != 614;
    if (WM_rowids.first is not null) then
    WM_found := 1;
    forall wm_indx in WM_rowids.first .. WM_rowids.last
    update T2.MCM_MESSAGE_lt
    set retireTime = wmsys.owm_9ip_pkg.activeTimeForDML,
    nextver = wmsys.lt_ctx_pkg.getNextVer(nextver,
    614,
    version,
    'denialletterspoc_onmzpulp',
    'CRS_LEAF',
    22)
    where rowid = WM_rowids(wm_indx)
    returning wmsys.lt_ctx_pkg.getCurNextVer bulk collect into WM_nextver;
    /* These are the rows that have not yet been resolved after the latest BeginResolve */
    forall wm_indx in WM_rowids.first .. WM_rowids.last execute immediate
    'begin insert into T2.MCM_MESSAGE_lt(ID,DPS_APPLICATION_ID,TYPE_ID,NAME,PRIVILEGED,CAMPAIGN_ID,TEXT,FILE_NAME,GRAPHIC_WIDTH,GRAPHIC_HEIGHT,DESCRIPTION,ACTIVE,INCLUDE_IN_LIBRARY,MESSAGE_FILTER_ID,OP_USER_ID,OP_WORKSPACE,OP_TIME,version,createtime,retiretime,nextver,delstatus,ltlock)
    (select t1.ID,t1.DPS_APPLICATION_ID,t1.TYPE_ID,t1.NAME,t1.PRIVILEGED,t1.CAMPAIGN_ID,t1.TEXT,t1.FILE_NAME,t1.GRAPHIC_WIDTH,t1.GRAPHIC_HEIGHT,t1.DESCRIPTION,t1.ACTIVE,t1.INCLUDE_IN_LIBRARY,t1.MESSAGE_FILTER_ID,t1.OP_USER_ID,t1.OP_WORKSPACE,t1.OP_TIME,614, wmsys.owm_9ip_pkg.activeTimeForDML, null,:1, sign(delstatus)*(abs(delstatus)+1),
    wmsys.lt_ctx_pkg.checkngetlock(''C'', t1.ltlock, t1.nextver, ''denialletterspoc_onmzpulp'', 0,
    ''MODIFY'', t1.version, t1.delstatus, 0, 1)
    from T2.MCM_MESSAGE_lt t1
    where t1.rowid = :2); end;'
    using WM_nextver(wm_indx),
    WM_rowids(wm_indx)
    ;
    end if;


    if (WM_found = 1) then
    wmsys.lt_ctx_pkg.update_modified_tables('T2',
    'MCM_MESSAGE',
    614,
    'denialletterspoc_onmzpulp',
    22);
    end if;
    if (WM_found = 1) then
    forall wm_indx in WM_rowids.first .. WM_rowids.last
    delete from T2.MCM_MESSAGE_aux
    where (ID) in (select ID
    from T2.MCM_MESSAGE_lt
    where rowid = WM_rowids(wm_indx))
    and childState = 'denialletterspoc_onmzpulp';
    end if;
    /* now insert new syncing information about these rows */
    execute immediate 'insert into T2.MCM_MESSAGE_aux (
    select ID,
    p.WM_childstate, p.WM_parentstate,
    p.WM_childds, 614, p.WM_parentds, p.WM_parentver, ''0'',''CC''

    from T2.MCM_MESSAGE_pkc p
    where ( id in (8403026,10134822,8259485,8259488,8426289,8426287,10186643,8426288) ) )';
    END;

    Thanks

    Amos
  • 3. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
    883548 Newbie
    Currently Being Moderated
    Hi, Ben

    And I traced this error after add save exceptions into forall codes. I get below variable:

    WM_nextver(1):-1
    WM_rowids(1):AAAfk3AA3AAKc84AAV
    WM_nextver(2):-1
    WM_rowids(2):AAAfk3AA5AAKsdnAAP
    WM_nextver(3):-1
    WM_rowids(3):AAAfk3AA0AAMoq1AAP
    WM_nextver(4):-1
    WM_rowids(4):AAAfk3AA3AAKc84AAU
    WM_nextver(5):-1
    WM_rowids(5):AAAfk3AA0AAFXHwAAj
    WM_nextver(6):-1
    WM_rowids(6):AAAfk3AA5AAJtXlAAN
    WM_nextver(7):-1
    WM_rowids(7):AAAfk3AA5AAKsdnAAA
    WM_nextver(8):-1
    WM_rowids(8):AAAfk3AA2AAIWX1AAA

    Number of statements that failed: 1
    Error #1 occurred during iteration #2
    Error message is ORA-00001: unique constraint (T2.MCM_MESSAGE_PK) violated
    WM_nextver: -1
    WM_rowids: AAAfk3AA5AAKsdnAAP

    I queried result in performed session and found first row has been insert successed. But the second row failed. I insert second row manually successed, then I rollbacked it. It seem that error haven't relationship with data but loop method. Forall syntax have some problem here.

    Our DB version is 10.2.0.4.0.

    Can you give me some advice?

    Thanks

    Amos

    Thanks

    Amos

    Edited by: user6533462 on Aug 22, 2011 12:46 AM
  • 4. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
    883548 Newbie
    Currently Being Moderated
    Hi, Ben

    I googled error with forall and ORA-1. Our error similar with Bug 7000281 "Difference in FORALL statement behaviour in 11g This note gives a brief overview bug 7000281". But I can't confirm it. Can you give us some advice?

    Thanks

    Amos
  • 5. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
    aschilling Newbie
    Currently Being Moderated
    hi Amit!

    it sounds a bit as if you are running into bug #12730297 (see oracle support for details). Depending on what operations internally are triggered by OWM during conflict resolution I assume this can occur as well (in our case it only occurred for RefreshTable).
    Some more details can also be found in this thread: False Unique Constraint Violation on RefreshTable
    We also had the strange situation that the violation fired although the constraint was disabled. As you are running the exact same version as our production system it's possible that it is the same bug.
    Important: our DBAs are still in contact with Oracle concerning this bug. The patch that was released due to our SR was not installable on 10.2.0.4.3, so we are still waiting for a new patch that works with our environment.
    I'll drop some lines here in the forum as soon as we have the patch and our DBAs installed it successfully.

    kind regards,

    Andreas
  • 6. Re: ORA-00001 and ORA-06512 error on  DBMS_WM.ResolveConflicts
    Ben Speckhard Pro
    Currently Being Moderated
    Hi Amos,

    This does look like it might be bug 7000281. It seems to also affect later versions of 10.2, so it might be worth filing an SR, to determine if in fact you are running into this bug. It certainly sounds like it, if converting the forall loop into a standard loop fixes the error.

    Also, bug 12730297 is strictly a refresh bug. It should not be caused by ResolveConflict errors.

    Regards,
    Ben

Legend

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