3 Replies Latest reply: Feb 16, 2011 10:18 AM by Ben Speckhard-Oracle RSS

    DBMS_WM.BeginResolve fails with ORA-20060 error

      Following is the scenario in which I am facing an issue with 'DBMS_WM.BeginResolve' prodecure in Oracle Workspace Manager.

      In my workflow I have generated huge number of conflicts in two workspaces. To resolve these conflicts I use a Windows GUI client which provides an user interface to resolve conflicts. This client internally loops through each conflicted table and issues following lines of code to resolve conflicts.

      BEGIN DBMS_WM.BeginResolve('122'); END;.

      BEGIN DBMS_WM.ResolveConflicts('122', 'SAMPLE.EMP_TABLE', 'EMP_ID in (''1111'')','CHILD'); END;

      BEGIN DBMS_WM.SetWoOverwriteOFF(); END;

      BEGIN DBMS_WM.GotoWorkspace('122'); END;

      BEGIN DBMS_WM.CommitResolve('122'); END;

      In a scenario my client has crashed after executing below line,
      BEGIN DBMS_WM.BeginResolve('122'); END;

      After the crash there was a Open Conflict Resolution session that has been left out in Oracle DB. Next time when I open my client to resolve conflicts I could never call the below line, as an open conflict resolution session already exists in that workspace and I get the following Ora- Error.
      BEGIN DBMS_WM.BeginResolve('122'); END;

      Oracle.DataAccess.Client.OracleException ORA-20060: user must call BeginResolve or have WM_ADMIN_ROLE to invoke RollbackResolve

      To avoid this what could be the best possible solution?

      I tried to resolve this issue by calling 'RollbackResolve', but it says user should have 'WM_ADMIN_ROLE' assigned.
      BEGIN DBMS_WM.RollbackResolve('122'); END;

      So in such kind of scenarios what could be the best possible solution? Is it assigning the 'WM_ADMIN_ROLE' and calling BEGIN DBMS_WM.RollbackResolve('122'); END;...but with this approach all users will have higher privileges...

      Please suggest.

      Thanks PV.
        • 1. Re: DBMS_WM.BeginResolve fails with ORA-20060 error
          Ben Speckhard-Oracle

          You have a number of options:

          1. Log in as the user that executed BeginResolve. Aside from users with the WM_ADMIN_ROLE, this is the only user allowed to execute RollbackResolve for that specific workspace. You can find the user that executed BeginResolve by using the following:

          SQL> select workspace, resolve_user from all_workspaces ;

          2. Create a definer's rights procedure in the schema that executed BeginResolve and grant execute privileges to the user that is going to be calling it.

          3. Create a separate schema and grant WM_ADMIN_ROLE to that schema that could then be used for executing RollbackResolve.

          • 2. Re: DBMS_WM.BeginResolve fails with ORA-20060 error
            Thanks a lot for the solutions.

            In addition to the above provided solutions I am thinking of caling BEGIN DBMS_WM.BeginResolve('3493888'); END; after crash...this is not giving an error...and I am assuming that this will use the orphaned conflict resolution session (which was created during a crash).

            Please suggest if there are any issues in calling BeginResolve() upon a crash.
            • 3. Re: DBMS_WM.BeginResolve fails with ORA-20060 error
              Ben Speckhard-Oracle

              If BeginResolve completes without any error, then that means that there is not an active conflict resolution session. It either completed normally, or had failed during the previous BeginResolve.

              You can always execute BeginResolve and if there is an error, you would know that you need to rollback the conflict resolution sessions using one of the previous solutions or continue using the currently active session, if possible.