oracle 锁查询

[code]先看一个数据库监控图：这上面的信息中，怎么判断XX会话被另一个会话堵塞呢？如果用网上的判断死锁sql去查询，类似如下：思考了一段时间，决定从gv$session入手，下面先看第一条sql1、查看活动会话信息--查看活动会话信息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,STATEFROM GV$SESSIONWHERE USERNAME IS NOT NULLAND 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,CASEWHEN STATE = \'WAITING\' THENSECONDS_IN_WAITWHEN STATE = \'WAITING KNOWN TIME\' THENWAIT_TIMEEND AS \"等待时间(s)\",STATEFROM GV$SESSIONWHERE USERNAME IS NOT NULLAND 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,CASEWHEN STATE = \"WAITING\" THENSECONDS_IN_WAITWHEN STATE = \"WAITING KNOWN TIME\" THENWAIT_TIMEEND AS \'等待时间(s),STATEFROM GV$SESSIONWHERE INST_ID = \"&INSTANCE\"and SID = \"&SID\";4、查看会话ID在执行什么sqlselect sid, sql_textfrom v$session a, v$sql bwhere sid in (&sid)and (b.sql_id = a.sql_id or b.sql_id = a.prev_sql_id);5、查看SQL_ID相关sqlselect listagg(sql_text, \" \") within group(order by piece)from v$sqltextwhere sql_id = \"&sqlid\"group by sql_id