How will be expressed to solve a lock by the lock in the database

Tuesday, March 03, 2009 by rain

When we are operating a database,Because the operation is undeserved,can cause database watch to be locked up to decide occasionally,So we often are at a loss,Do not know how to solve a lock to these watches,In Pl/sql Developer tool menu " Tools " inside " Sessions " can inquire the conversation that exists now,But we find that conversation very hard to was locked up to decide,It is more difficult to want to find the conversation that is locked up so,This makes inquire the statement can inquire a conversation that is locked up so below.As follows:

SELECT Sn.username, m.SID, sn.SERIAL# , m.TYPE,
DECODE (m.lmode,
0, 'None' ,
1, 'Null' ,
2, 'Row Share' ,
3, 'Row Excl. ' ,
4, 'Share' ,
5, 'S/Row Excl. ' ,
6, 'Exclusive' , Lmode, LTRIM (TO_CHAR (lmode, '990') )
) Lmode,
DECODE (m.request,
0, 'None' ,
1, 'Null' ,
2, 'Row Share' ,
3, 'Row Excl. ' ,
4, 'Share' ,
5, 'S/Row Excl. ' ,
6, 'Exclusive' , Request, LTRIM (TO_CHAR (m.request, '990') )
) Request, M.id1, m.id2
FROM V$session Sn, v$lock M
WHERE (sn.SID = M.SID AND M.request! = 0) - - existence locks up a request,Namely by block
OR ( Sn.SID = M.SID - - nonexistent lock requests,But the object that the lock decides is locked up to decide by other and conversational request
AND M.request = 0
AND Lmode! = 4
AND (id1, id2) IN (
SELECT S.id1, s.id2
FROM V$lock S
WHERE Request! = 0 AND S.id1 = M.id1
AND S.id2 = M.id2)
AND S.id2 = M.id2)
ORDER BY Id1, id2, m.request;