注释： 该SQL可查询多会话,非select的DML操作,同时操作A表引起的锁 ..会话之间的关系~ ….下文有该SQL用到的视图/字段的详细
注释：
该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#若
本文来源gao!%daima.com搞$代*!码9网(
操作完的该字段值=-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)
本文永久更新链接地址：
转载请注明原文链接：Oracle 查询锁之间的依赖关系
