Oracle 查询锁之间的依赖关系

注释：该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 == 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）更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址