Friday, 30 May 2014

How to find out the user who locked the row?

n some particular situations, we need to know the user-id who has acquired a lock on a table’s row and not yet released it. The following steps can give you an opportunity to find out this information.
1] Connect to SYS user.
2] Make use of the views: v$lock and v$session
v$lock gives you the SID which acquire a TX or TM type of lock if the table is locked by this session and v$session gives you the information of username who is running that session. Remember SID 2 & 3 in v$lock are for oracle background process, which you should not be concerned about.
3] Run the Query:

SELECT B.SID,
         C.USERNAME,
         C.OSUSER,
         C.TERMINAL,
         DECODE (B.ID2, 0, A.OBJECT_NAME, 'Trans-' || TO_CHAR (B.ID1))
            OBJECT_NAME,
         B.TYPE,
         DECODE (B.LMODE,
                 0, '--Waiting--',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl',
                 4, 'Share',
                 5, 'Sha Row Exc',
                 6, 'Exclusive',
                 'Other')
            "Lock Mode",
         DECODE (B.REQUEST,
                 0, ' ',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl',
                 4, 'Share',
                 5, 'Sha Row Exc',
                 6, 'Exclusive',
                 'Other')
            "Req Mode"
    FROM DBA_OBJECTS A, V$LOCK B, V$SESSION C
   WHERE A.OBJECT_ID(+) = B.ID1
       AND B.SID = C.SID
       AND C.USERNAME IS NOT NULL
ORDER BY B.SID, B.ID2;

No comments:

Post a Comment