oracle查看session阻塞,如何查看Oracle数据库的session阻塞

我们都知道Oracle自己会自动处理死锁的情况，不需要人为的干预，但是Oracle不能自己处理session阻塞的的情况，阻塞导致资源的浪费和消耗系统性能，这个时候我们就需要快速的找出导致阻塞的原因，并尽快排除它，好让系统重新正常运行。下面我将做一个例子来解释如何迅速的处理这种阻塞：

下面的例子是两个session 同时更新HR用户的同一条记录。

HR用户窗口1：

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