8 Replies Latest reply: Feb 18, 2013 10:17 AM by Ben Speckhard-Oracle RSS

    Unlock Rows in LIVE workspace

    rcx4009v2
      Hi,

      Is there a way to unlock rows locked by other users using the Schema Owner account?

      I tried to execute DBMS_WM.unlockrows('LIVE','ROADS_GEOM','FEATID > 0','USER','ES'); and DBMS_WM.unlockrows('LIVE','ROADS_GEOM','FEATID > 0','ALL','ES');

      But when I checked the _LOCK views, there are still Locked Rows by other Users in LIVE Workspace.

      Thanks in Advance.

      Edited by: rcx4009v2 on Jan 29, 2013 8:02 PM
        • 1. Re: Unlock Rows in LIVE workspace
          Stefan Jager
          Hi RCX,

          Have you tried
          DBMS_WM.unlockrows('NONE','ROADS_GEOM','','ALL','VE'); 
          Documentation says:A value of NONE can be used if lock_mode is set to VE (version-exclusive). This causes rows locked by any workspace to be unlocked.

          If the where_clause parameter is not specified, all rows in table_name are made accessible.
          Also: when you tried to unlock, was everybody else logged out an were there no more sessions active?

          HTH,
          Stefan
          • 2. Re: Unlock Rows in LIVE workspace
            rcx4009v2
            I tried DBMS_WM.unlockrows('NONE','ROADS_GEOM','','ALL','VE');

            Still _LOCK has rows.

            Is it required that everybody is logged out when unlocking rows? Some users are connected 24/7 to the db.
            • 3. Re: Unlock Rows in LIVE workspace
              Stefan Jager
              rcx4009v2 wrote:
              Some users are connected 24/7 to the db.
              24/7? Wow. What kind of users are those?

              Anyway, I would think that if a user is still connected, and has not merged some of their changes, that the rows would be locked no matter how hard you try to unlock them.

              But which kind of locks are still left? Have you checked that? Maybe the VE-lock_mode is not the correct one.

              Stefan
              • 4. Re: Unlock Rows in LIVE workspace
                rcx4009v2
                But the Workspace where the rows were lock are on the LIVE workspace, so I believe no merging is needed for that?

                Based on the ALL_WM_LOCKED_TABLES, the lock is Exclusive. So I can only unlock those rows by executing UnlockRows using the Lock Owner and not with the schema Owner?
                • 5. Re: Unlock Rows in LIVE workspace
                  Stefan Jager
                  Hi RCX,

                  To be honest I'm not sure. But nothing prevents you from trying :-)

                  Stefan
                  • 6. Re: Unlock Rows in LIVE workspace
                    Ben Speckhard-Oracle
                    Hi,

                    To be able to unlock rows that have been locked by a different user, the user currently needs to have WM_ADMIN_ROLE (or be the SYS user). The other users do not have to be logged out, but the rows cannot have been locked in a uncommitted transaction, otherwise the rows will not be able to be queried by the other session.

                    Regards,
                    Ben
                    • 7. Re: Unlock Rows in LIVE workspace
                      rcx4009v2
                      Hi Ben,

                      Thanks for the response. I tried your suggestion regarding using a user with WM_ADMIN_ROLE privilege, but I still have no luck with regards to unlocking of rows in my LIVE workspace.
                      I can only unlock the rows when I execute DBMS_WM.UnlockRows using the Lock owner account.
                      The problem is that there are too many users in the Database and it is not right that i'll have each one of them unlock rows locked by their user everytime a lock occurs.
                      • 8. Re: Unlock Rows in LIVE workspace
                        Ben Speckhard-Oracle
                        Hi,

                        If it is not working as expected, I would recommend to file a SR. Not sure if it applies to your situation, but remember that roles are not used within a definer's rights procedure.

                        Regards,
                        Ben