Oracle 查询锁之间的依赖关系

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        ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223

–V$SQL            ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3043.htm#REFRN30246

–V$LOCK           ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121

–V$LOCKED_OBJECT  ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2030.htm#REFRN30125

–ALL_OBJECTS      ==    http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1158.htm#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)