注释：
该sql可查询多会话,非select的dml操作,同时操作a表引起的锁 ..会话之间的关系~
....下文有该sql用到的视图/字段的详细注释..
....若想显示其他字段可以按照自己需求增加 ..
sql:
select distinct s.sid , /*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的sid。*/
--s.serial# /*会话的序号*/,
s.state /*wait state~*/ ,
s.blocking_session ,
--session identifier of the blocking session. this column is valid only if blocking_session_status has the value valid.
s.blocking_session_status status, /*this column provides details on whether there is a blocking session: */
( case
when sql_text is null /*lo.request = 0 */
then
'(sid:' || s.sid || ')会话 sql已跑完'
else
'(sid:' || s.sid || ')会话 正执行sql：' || sql_.sql_text
end ) sql_text /*执行完的sql'sql_text标记sql已跑完，否则标记sql'*/ ,
--sql_.sql_fulltext sql全文本,
s.username /*创建该会话的用户名*/ ,
o.owner || '.' || o.object_name 锁的对象, --v$session.row_wait_obj#若操作完的该字段值=-1，，所以关联的v$locked_object取锁表
lo.request , -- lock mode in which the process requests the lock 会话申请的锁的模式
s.event ,
s.machine /*客户端的机器名。*/ ,
s.logon_time /*登陆时间*/ ,
'alter system kill session ''' || s.sid || ',' || s.serial# || ''';' kill --若存在锁情况,会用到kill锁释放~
from v$session s
left join v$sql sql_
on sql_.sql_id = s.sql_id
join v$locked_object l
on l.session_id = s.sid
join all_objects o
on l.object_id = o.object_id
join v$lock lo
on (lo.block != 0 or lo.request != 0 )
--v$lock.block => a value of either 0 or 1, depending on whether or not the lock in question is the blocker
--v$lock.request => lock mode in which the process requests the lock:下文有值的意义~ ['0 - none']
where lo.sid = l.session_id
and lo.sid = s.sid
order by s.blocking_session desc ;
注释：
--视图==官网注释
--v$session == #refrn30223
--v$sql == #refrn30246
--v$lock == #refrn30121
--v$locked_object == #refrn30125
--all_objects == #refrn20146
--显示字段==官网注释：
v$session.state = wait state :
--waiting - session is currently waiting
--waited unknown time - duration of the last wait is unknown; this is the value when the parameter timed_statistics is set to false
--waited short time - last wait was less than a hundredth of a second
--waited known time - duration of the last wait is specified in the wait_time column s.blocking_session ,
--session identifier of the blocking session. this column is valid only if blocking_session_status has the value valid.
v$session.blocking_session_status = this column provides details on whether there is a blocking session :
--valid - there is a blocking session, and it is identified in the blocking_instance and blocking_session columns
--no holder - there is no session blocking this session
--not in wait - this session is not in a wait
--unknown - the blocking session is unknown
v$lock.request = lock mode in which the process requests the lock :
--0 - none
--1 - null (null)
--2 - row-s (ss)
--3 - row-x (sx)
--4 - share (s)
--5 - s/row-x (ssx)
--6 - exclusive (x)
本文永久更新链接地址：