查询锁及并发锁等待

--锁等待信息（以上查询结果是一个树状结构，如果有子节点，则表示有等待发生)   

SELECT /*+ rule */  
 (SELECT '''' || TT.SID || ',' || SS.SERIAL# || ''' (' || SS.MACHINE || ')'  
    FROM V$LOCK TT, V$SESSION SS  
   WHERE TT.BLOCK = 1  
     AND TT.SID = SS.SID  
     AND TT.ID1 = (SELECT T.ID1 FROM V$LOCK T WHERE S.LOCKWAIT = T.KADDR)) AS BLOCKED_BY_SID#,  
 decode(l.LOCKED_MODE,0,'None', 1,'Null', 2,'Row-S', 3,'Row-X', 4,'Share', 5,'S/Row-X', 6,'Exclusive', 'Unknown')  LockMode,  
 LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,  
 O.OWNER,  
 O.OBJECT_NAME,  
 S.MODULE,  
 S.MACHINE,  
 O.OBJECT_TYPE,  
 S.SID,  
 S.SERIAL#  
  FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S  
 WHERE L.OBJECT_ID = O.OBJECT_ID  
   AND L.SESSION_ID = S.SID  
 ORDER BY O.OBJECT_ID, XIDUSN DESC;  

--当前锁
SELECT a.inst_id,a.oracle_username, c.sid,c.machine,c.osuser,
    decode(a.locked_mode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_type,
    b.owner,b.object_name, a.xidusn, a.xidslot, a.xidsqn
 FROM gv$locked_object a, all_objects b, gv$session c
 WHERE a.object_id = b.object_id AND a.inst_id=c.inst_id and a.session_id = c.sid;