ORACLE数据库如何查看死锁?

Question

ORACLE数据库如何查看死锁

Answer 1

SELECT substr(v$lock.sid,1,4) "SID",
      substr(username,1,12) "UserName",
      substr(object_name,1,25) "ObjectName",
      v$lock.type "LockType",
      decode(rtrim(substr(lmode,1,4)),
      '2','Row-S (SS)','3','Row-X (SX)',
      '4','Share',     '5','S/Row-X (SSX)',
      '6','Exclusive', 'Other' ) "LockMode",
      substr(v$session.program,1,25) "ProgramName"
FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION
WHERE (OBJECT_ID = v$lock.id1
     AND v$lock.sid = v$session.sid
     AND username IS NOT NULL
     AND username NOT IN ('SYS','SYSTEM')
     AND SERIAL# != 1);