我們都知道ORACLE自己會自動處理死鎖的情況，不需要人為的幹預，但是ORACLE不能自己處理session阻塞的的情況，阻塞導緻資源的浪費和消耗系統性能，這個時候我們就需要快速的找出導緻阻塞的原因，并盡快排除它，好讓系統重新正常運作。下面我将做一個例子來解釋如何迅速的處理這種阻塞：
下面的例子是兩個session 同時更新HR使用者的同一條記錄。
HR使用者視窗1：
[[email protected] ~]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 18 14:57:27 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> update hr.employees set last_name='b' where employee_id=100;
1 row updated.
HR使用者視窗2：
[[email protected] ~]$ sqlplus hr/hr
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 18 14:56:49 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> update hr.employees set last_name='a' where employee_id=100;
此時進入等待……hang住不動了
在打開一個視窗，用SYS使用者登陸：
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Dec 18 14:58:31 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
---查找阻塞，和被阻塞session id
SQL>select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid
||','||d.serial# block_msg, a.block from v$lock a,v$lock b,v$session c,v$session d
3 where a.id1=b.id1 and a.id2=b.id2 and a.block>0 and a.sid <>b.sid and a.sid=c.sid and b.sid=d.SID;
BLOCK_MSG
--------------------------------------------------------------------------------
BLOCK
----------
pts/2 ('151,18') is blocking 133,614
1
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
133 614 HR
134 71 SYS
138 298 SYSMAN
139 2 SYSMAN
140 2 SYSMAN
142 59 DBSNMP
147 175 DBSNMP
151 18 HR
152 1639 SYS
153 26 SYSMAN
159 13 SYS
11 rows selected.
---kill the blocker '151,8'
SQL> alter system kill session'151,18';
System altered.
再次回到視窗1：
SQL> select * from employees;
select * from employees
*
ERROR at line 1:
ORA-00028: your session has been killed
再次回到HR使用者視窗2檢視，語句已經執行了（提示1 row updated）：
SQL> update hr.employees set last_name='a' where employee_id=100;
1 row updated.