3 Replies Latest reply: Apr 16, 2010 2:05 PM by Ben Speckhard-Oracle RSS

    createworkspace : WM_ERROR_156 there are active sessions in the workspace

    763539
      Hy all again,

      Another strange situation that I've encountered lately. In our system we're using WM to handle long tranactions. When a new long transaction starts, we create a new workspace, make a couple of DML statements, and when finishing the long transaction we merge the workspace into the parent workspace, then remove the user created workspace. All user created workspace's parent is the LIVE workspace and all of them are contiunally refreshed with 'S' locking. The rest are the default values.

      Normally it works well, but now I cannot make a new workspace. The dbms_wm.createworkspace('myWorkspace') throws the "there are active sessions in the workspace 'otherWorkspace' " exception. I ran the command from the LIVE workspace. We did a lot of DDL since we started to use the OWM, even dropped and regenerated whole schemas, and haven't got that kind off error this far. To stay at the example, what does 'myWorkspace' have to do with the 'otherWorkspace'? They would both descend from the LIVE workspace, but thats all I think. Are there any rules that I'm not aware of? I could easily create a new workspace before, even if another session used one of it's 'sister' workspaces.

      Btw, I'm using 11.1.0.7.0.

      Thanks in advance.
      Viktor
        • 1. Re: createworkspace : WM_ERROR_156 there are active sessions in the workspace
          NoelKhan
          Viktor,

          . . . .I've only seen ORA-20156 ('active sessions') with MergeWorkspace and RemoveWorkspace calls. If you tried to CreateWorkspace('otherWS') and otherWS already existed (anywhere in the Workspace tree), then you should see ORA-20023 ('WS already exists').

          . . . .To reproduce ORA-20156 with Merge- or Remove- Worspace calls:
          /* Oracle Session 1 */
          SQL> exec dbms_wm.Createworkspace('Parent');
          
          PL/SQL procedure successfully completed.
          
          SQL> exec dbms_wm.GotoWorkspace('Parent');
          
          PL/SQL procedure successfully completed.
          
          SQL> exec dbms_wm.Createworkspace('Child');
          
          PL/SQL procedure successfully completed.
          
          SQL> exec dbms_wm.GotoWorkspace('Child');
          
          PL/SQL procedure successfully completed.
          
          /* Then, while Session 1 is still open,
          . . begin Oracle Session 2              */
          
          -- Raise ORA-20156 upon MergeWorkspace
          exec dbms_wm.MergeWorkspace('Child', TRUE, TRUE, TRUE);
          BEGIN dbms_wm.MergeWorkspace('Child', TRUE, TRUE, TRUE); END;
          
          *
          ERROR at line 1:
          ORA-20156: there are active sessions in the workspace: 'Child'
          ORA-06512: at "SYS.LT", line 5945
          ORA-06512: at line 1
          
          -- Raise ORA-20156 upon RemoveWorkspace
          BEGIN dbms_wm.RemoveWorkspace('Child'); END;
          
          *
          ERROR at line 1:
          ORA-20156: there are active sessions in the workspace: 'Child'
          ORA-06512: at "SYS.LT", line 3700
          ORA-06512: at "SYS.LT", line 4915
          ORA-06512: at line 1
          
          /* However, if you try to CreateWorkspace('Child')
          from Session 1 or 2, you get ORA-20023 (already exists) */
          
          -- from Session 2
          SQL> exec dbms_wm.CreateWorkspace('Child');
          BEGIN dbms_wm.CreateWorkspace('Child'); END;
          
          *
          ERROR at line 1:
          ORA-20023: a workspace already exists with the name: 'Child'
          ORA-06512: at "SYS.LTADM", line 802
          ORA-06512: at "SYS.LTADM", line 782
          ORA-06512: at "SYS.LT", line 4801
          ORA-06512: at line 1
          
          --from Session 1
          BEGIN dbms_wm.CreateWorkspace('Child'); END;
          
          *
          ERROR at line 1:
          ORA-20023: a workspace already exists with the name: 'Child'
          ORA-06512: at "SYS.LTADM", line 802
          ORA-06512: at "SYS.LTADM", line 782
          ORA-06512: at "SYS.LT", line 4801
          ORA-06512: at line 1
          . . . .Based on this demonstration, [1] I don't see how CreateWorkspace('otherWS') could possibly result in the 'active session error' and [2] the culprit is likely a Merge- or Rollback- Workspace call.

          . . . .Do you have code that can reproduce the scenario you've described?

          Regards,
          Noel
          • 2. Re: createworkspace : WM_ERROR_156 there are active sessions in the workspace
            763539
            Hy!

            Yes, session locking on workspaces makes sense on merge, remove and rollback, already learned that on my tries when I've first encountered them. I only opened this thread in this forum, because I have found no documentation about this, and thinking again, it's a really uncommon behaviour. I want to know what is the cause of it.

            I checked the session by the workspace's name, found one of my colleagues uses it through our client software. When I asked him to disconnect, the problem solved.

            In another instance, I've tried to reproduce the issue with no luck. My last tought about this that somebody would made DDL modifications on version enabled tables, when some other sessions were active in some of the child workspaces. It could be also possible, that not all of these sessions have been killed so far. When it happened, OWM might resolved the DDL, make createworkspace possible again... but this is just a theory, haven't tested yet.

            At Monday, I'll try to reproduce the situation with the DDL modifications, but not now, it is Friday, 17:30... so enough work for the week ;)

            Regards,
            Viktor
            • 3. Re: createworkspace : WM_ERROR_156 there are active sessions in the workspace
              Ben Speckhard-Oracle
              Hi Viktor,

              Generally, OWM only needs an exclusive lock on the parent workspace while creating the child workspace. Sibling workspaces would not cause the _156 error.

              However, when the CR_WORKSPACE_MODE and NONCR_WORKSPACE_MODE system parameters are different values, and you are creating the 1st refreshed or the 1st non-refreshed child workspace within the system, then OWM needs to potentially lock all of the child workspaces while it refreshes the cached system parameters. That is why we do not allow sessions to be in any other child workspace.

              Regards,
              Ben