ORACLE查询死锁

用SYS用户登录数据库！

查看锁表进程SQL语句1：

select sess.sid,

   sess.serial#,

   lo.oracle_username,

   lo.os_user_name,

   ao.object_name,

   lo.locked_mode

   from v$locked_object lo,

   dba_objects ao,

   v$session sess

where ao.object_id =lo.object_id and lo.session_id = sess.sid;

 

查看锁表进程SQL语句2：

SELECT DISTINCT S.SID , /*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/

 

                --S.SERIAL# /*会话的序号*/,

 

                S.STATE /*WAIT STATE~*/ ,

 

                S.BLOCKING_SESSION ,

 

                --SESSION IDENTIFIER OF THEBLOCKING SESSION. THIS COLUMN IS VALID ONLY IF BLOCKING_SESSION_STATUS HAS THEVALUE VALID.

 

                S.BLOCKING_SESSION_STATUSSTATUS, /*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 requeststhe lock 会话申请的锁的模式

 

                S.EVENT  ,

 

                S.MACHINE /*客户端的机器名。*/ ,

 

                S.LOGON_TIME/*登陆时间*/ ,

 

                'ALTER SYSTEMKILL 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 or1, 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 ;