session lock

對于9i:
1. select * from v$lock where block = 1;
可查到當前占有鎖而block了其他session的session.

2. 可查出誰正block了誰:
select a.SID “Blocking Session”, b.SID “Blocked Session”
  from v$lock a, v$lock b
  where a.SID != b.SID
  and a.ID1 = b.ID1
  and a.ID2 = b.ID2
  and b.request > 0
  and a.block = 1;

3.  可查出處於等待狀態的session具體等待的行：
 select dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
 from v$session where sid=126;
 
select * from test1 where rowid = 'AAAYAxAAFAAAAA2AAA'

對於10g, 比較簡單了：

select blocking_session "is blocking", sid "is blocked" from v$session where blocking_session > 0 ;

blocking_session block 了 sid.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24596137/viewspace-717311/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24596137/viewspace-717311/