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,
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