查询被锁的会话
通常我们会遇到有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