oracle 锁查询

先看一个数据库监控图：这上面的信息中，怎么判断XX会话被另一个会话堵塞呢？
如果用网上的判断死锁sql去查询，类似如下：思考了一段时间，决定从gv$session入手，下面先看第一条sql

1、查看活动会话信息
--查看活动会话信息
SELECT INST_ID,
       SID,
       SERIAL#,
       PROCESS,
       USERNAME,
       LOCKWAIT,
       SCHEMANAME,
       OSUSER,
       MACHINE,
       PROGRAM,
       SQL_ID,
       MODULE,
       ACTION,
       LOGON_TIME,
       BLOCKING_SESSION_STATUS,
       BLOCKING_INSTANCE,
       BLOCKING_SESSION,
       EVENT,
       WAIT_CLASS,
       SECONDS_IN_WAIT,
       STATE
  FROM GV$SESSION
 WHERE USERNAME IS NOT NULL
   AND STATUS = 'ACTIVE'
 ORDER BY LOGON_TIME, SID;

2、查看当前被阻塞lock信息(waiter)

--注：如果state值为Waiting，那么wait_time值无用。Second_in_wait值是实际的等待时间(单位：秒)。
--如果state值为Wait unknow time，那么wait_time值和Second_in_wait值都无用。
--如果state值为Wait short time，那么wait_time值和Second_in_wait值都无用。
--如果state值为Waiting known time，那么wait_time值就是实际等待时间(单位：秒)，Second_in_wait值无用。

SELECT INST_ID "实例ID",
       SID,
       SERIAL#,
       PROCESS,
       USERNAME,
       SCHEMANAME,
       OSUSER,
       MACHINE,
       PROGRAM,
       SQL_ID,
       MODULE,
       ACTION,
       LOCKWAIT,
       BLOCKING_SESSION_STATUS,
       BLOCKING_INSTANCE "阻塞会话实例",
       BLOCKING_SESSION "阻塞会话SID",
       EVENT "等待事件",
       WAIT_CLASS,
       LOGON_TIME,
       CASE
         WHEN STATE = 'WAITING' THEN
          SECONDS_IN_WAIT
         WHEN STATE = 'WAITING KNOWN TIME' THEN
          WAIT_TIME
       END AS "等待时间(s)",
       STATE
  FROM GV$SESSION
 WHERE USERNAME IS NOT NULL
   AND STATUS = 'ACTIVE'
   AND LOCKWAIT IS NOT NULL;

因为想尽量放在一条sql把需要的信息都展示出来，所以字段会比较多。

3、查看阻塞会话SID信息（holder）
--考虑到多实例会存在sid一致的情况，所以加了实例区分
SELECT INST_ID "实例ID",
       SID,
       SERIAL#,
       PROCESS,
       USERNAME,
       SCHEMANAME,
       OSUSER,
       MACHINE,
       PROGRAM,
       SQL_ID,
       MODULE,
       ACTION,
       LOCKWAIT,
       BLOCKING_SESSION_STATUS,
       BLOCKING_INSTANCE,
       BLOCKING_SESSION "阻塞会话SID",
       EVENT "等待事件",
       WAIT_CLASS,
       LOGON_TIME,
       CASE
         WHEN STATE = "WAITING" THEN
          SECONDS_IN_WAIT
         WHEN STATE = "WAITING KNOWN TIME" THEN
          WAIT_TIME
       END AS '等待时间(s),
       STATE
  FROM GV$SESSION
 WHERE INST_ID = "&INSTANCE"
   and SID = "&SID";

4、查看会话ID在执行什么sql
select sid, sql_text
  from v$session a, v$sql b
 where sid in (&sid)
   and (b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id);

5、查看SQL_ID相关sql
select listagg(sql_text, " ") within group(order by piece)
  from v$sqltext
 where sql_id = "&sqlid"
 group by sql_id