How to find block sql from dba_waiters v$session v$sql

查询被锁的会话
通常我们会遇到有session被其他session block的状态,DBA需要找出这些block与被block的session及其sql,并删除导致blocking的session.
通过以下3个视图可以帮助我们解决这个问题.
dba_waiters
v$session
v$sql

例子-1
1,在session 140下查询表的信息如下.
SQL>select distinct sid from v$mystat;
       SID
----------
       140
SQL>select * from sales where id=142;
        ID NAME
---------- --------------------
       142 Jerron Peng
       142 Jerron Peng

2,在session 148下删除这2行记录,但未提交.
SQL>delete sales where id=142;
2 rows deleted.

3,在session 157下更新这2行记录,此时被block住.
SQL>update sales set name='Jeron Peng' where id=142;

4,dba_waiters找出系统在等待的session
SQL>select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE            MODE_HELD            MODE_REQUESTED         LOCK_ID1   LOCK_ID2
--------------- --------------- -------------------- -------------------- -------------------- ---------- ----------
            157             148 Transaction          Exclusive            Exclusive                524328       6900
结果显示:
a,session 157处于等待中,意味着这个session被block住了.
b,HOLDING_SESSION为148,代表148是导致block的根源.

5,查询sid为148,引起其他session blocking的sql
SQL>select prev_sql_id from v$session where sid=148;
PREV_SQL_ID
-------------
6ubd0c60r1tr9
SQL>select sql_text from v$sql where sql_id='6ubd0c60r1tr9';//查询引起blocking的会话最后执行的sql语句.有可能是这条语句引起的blocking.

6,查询被blocking的会话的sql
SQL>select sql_id from v$session where sid=157;
SQL_ID
-------------
9thzhd6tsyaug
SQL>select sql_text from v$sql where sql_id='9thzhd6tsyaug';
SQL_TEXT
--------------------------------------------------------------------------------
update sales set name='Jeron Peng' where id=142

7,最后只需kill掉引起blocking的session即可,这里需要kill掉148即可.
SQL>select SID,SERIAL#,STATUS,USERNAME,COMMAND,CLIENT_IDENTIFIER from v$session where sid=148;
       SID    SERIAL# STATUS   USERNAME                COMMAND CLIENT_IDENTIFIER
---------- ---------- -------- -------------------- ---------- ------------------------------
       148        603 INACTIVE HR                            0
SQL>alter system kill session '148,603';
System altered.

8,验证dba_waiters
SQL>select * from dba_waiters;

查询被锁的表
v$process
v$session
v$locked_object
all_objects

select p.spid,
       a.serial#, 
       c.object_name,
       b.session_id,
       b.oracle_username,
       b.os_user_name 
from   v$process p,v$session a, v$locked_object b,all_objects c 
where  p.addr=a.paddr and 
       a.process=b.process and 
       c.object_id=b.object_id