oracle查看阻塞的sql,ORACLE查询系统中阻塞情况~

在ORACLE中经常会碰到阻塞的情况发生，这个时候我们就需要快速的找出导致阻塞的原因，并尽快排除它，好让系统重新正常运行。

下面以死锁为例，来看看如何找出导致阻塞的会话并解决问题。

//SCOTT窗口1

SQL> select * from t2;

ID

----------

3

1

2

SQL> update t2 set id=12 where id=2;

1 row updated.

SQL>

//SCOTT窗口2

SQL> select * from t2;

ID

----------

3

1

2

SQL> update t2 set id=11 where id=3;

已更新 1 行。

SQL> update t2 set id=13 where id=2;

//此时进入等待……卡住不动了

//SYS窗口

SQL> select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid

||','||d.serial# block_msg, a.block

2 from v$lock a,v$lock b,v$session c,v$session d

3 where a.id1=b.id1

4 and a.id2=b.id2

5 and a.block>0

6 and a.sid <>b.sid

7 and a.sid=c.sid

8 and b.sid=d.SID

9 ;

BLOCK_MSG BLOCK

---------------------------------------- ----------

HWANG ('138,305') is blocking 153,15 1

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME

---------- ---------- ------------------------------

136 179 SYS

138 305 SCOTT

153 15 SCOTT

//这个时候可以杀掉那个BLOCKER

SQL> alter system kill session'138,305';

System altered.

//SCOTT窗口1

SQL> select * from t2;

select * from t2

*

ERROR at line 1:

ORA-00028: your session has been killed

SQL>