Oracle中診斷阻塞session的方法 blocking error

由於鎖的機制,當某一條DML或者DDL SQL語句執行被阻塞的時候,需要找出是什麼原因導致這條SQL被阻塞了,下面介紹一下通常的診斷方法:
 
假設有這樣一個表: table t(id int primary key,val int);資料為:
 
id  val
1   1
2    2
1,在一個Session,這裡把它叫做Session A,做了如下的update語句,沒有提交或者復原.
SQL> update t set val = 3 where id=1; 
 
2,在一另一個Session,這裡把它叫做Session B,做了如下的update語句,Session B會被阻塞.
SQL> update t set val = 4 where id=1;
 
但有活動事務對對象加鎖的時候,會在v$locked_object視圖中有記錄如object_id,session_id等,通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN欄位都為空白.中session_id為139的是被阻塞的session.
 
select * from v$locked_object;

 
select dbo.* from  v$locked_object lo ,dba_objects dbo  where lo.object_id = dbo.object_id and lo.xidusn=0

 
通過查詢v$lock可以看到是哪一個session阻塞了哪一個session:142阻塞了139
 
with blkedsess as (select * from v$lock where request !=0)
select  blkingsess.sid blockingsid, blkedsess.sid blockedsid
from v$lock blkingsess,blkedsess
where blkingsess.id1 = blkedsess.id1
and blkingsess.id2 = blkedsess.id2
and blkingsess.sid != blkedsess.sid

 
在通過v$session可以查到session相關的資訊,被阻塞的status一般為ACTIVE,還可以通過sql_address聯合v$sql找到被阻塞的SQL語句.   
 
select sid,serial#,status,sql_address from v$session where sid in(139,142)

 
select * from V$sql where address='6BE7D33C'
 
or
select sql_text, sql_fulltext, sql_id from v$sqlarea where sql_id='6BE7D33C';
 
or
select sql_text from v$sqltext where sql_id = '6BE7D33C';
 

 
這時候DBA可以聯絡造成阻塞的session結束事務或者根據情況用命令終止session
 
alter system kill session '142,7'; 其中142為sid,7為serial#
 
session 142會收到如下錯誤,而session139往下執行後續步驟.
ERROR:
ORA-03114: not connected to Oracle