如何查看Oracle資料庫的session阻塞

我們都知道Oracle自己會自動處理死結的情況，不需要人為的幹預，但是Oracle不能自己處理session阻塞的的情況，阻塞導致資源的浪費和消耗系統效能，這個時候我們就需要快速的找出導致阻塞的原因，並儘快排除它，好讓系統重新正常運行。下面我將做一個例子來解釋如何迅速的處理這種阻塞：

下面的例子是兩個session 同時更新HR使用者的同一條記錄。

HR使用者視窗1：

[oracle@bkjia ~]$ 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：

[oracle@bkjia ~]$ 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使用者登陸：

[oracle@bkjia ~]$ 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.