oracle监控数据库的锁,oracle锁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 ;