注释： 该SQL可查询多会话,非select的DML操作,同时操作A表引起的锁 ..会话之间的关系~....下文有该SQL用到的视图/字段的详细注释......若想显示其他字段可以按照自己需求增加 ..
SQL:SELECTDISTINCTS.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: */ (CASEWHENSQL_TEXTISNULL/*LO.REQUEST = 0 */THEN'(SID:'|| S.SID ||')会话 SQL已跑完'ELSE'(SID:'|| S.SID ||')会话 正执行SQL：'|| SQL_.SQL_TEXTEND) 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锁释放~FROMV$SESSION SLEFTJOINV$SQL SQL_ONSQL_.SQL_ID = S.SQL_IDJOINV$LOCKED_OBJECT LONL.SESSION_ID = S.SIDJOINALL_OBJECTS OONL.OBJECT_ID = O.OBJECT_IDJOINV$LOCK LOON(LO.BLOCK !=0ORLO.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']WHERELO.SID = L.SESSION_IDANDLO.SID = S.SIDORDERBYS.BLOCKING_SESSIONDESC;

注释：--视图==官网注释
--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 =Waitstate :
--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 = Thiscolumnprovidesdetailsonwhether thereisa blockingsession:
--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 =Lockmodeinwhichtheprocess requeststhelock:
--0 - none--1 - null (NULL)--2 - row-S (SS)--3 - row-X (SX)--4 - share (S)--5 - S/Row-X (SSX)--6 - exclusive (X)


祝好~