[QUERY]ORACLE DEADLOCK 조회 및 KILL

http://blog.naver.com/jaekodog/100045483315

Test DB : Oracle 8.1.7 (Toad 8.0)

 

1. Lock이 걸린  사용자를 조회

   (Type의 값이 TM인 것이 DEAD LOCK이다.)   

   (Lock이 걸린 쿼리도 같이 조회하려면 ,"V$SQLTEXT" 관련 쿼리를 넣어준다)

 

SELECT  "V$LOCK"."TYPE"

             ,"V$SESSION"."SID"

             ,"V$SESSION"."SERIAL#" 

             ,"V$SESSION"."MACHINE"

             ,"V$SESSION"."TERMINAL"

             ,"V$SESSION"."OSUSER"

             ,"V$SESSION"."USERNAME"

             ,"V$SESSION"."PROGRAM"

             ,"OBJ"."OBJECT_NAME"

             ,"V$LOCK"."ID1"

             ,"V$LOCK"."REQUEST"

             ,"V$SESSION"."STATUS"

        --  ,"V$SQLTEXT"."SQL_TEXT"   

  FROM  "OBJ"

            ,"V$LOCK"

            ,"V$SESSION"

       --  ,"V$SQLTEXT"

 WHERE ( v$session.sid = v$lock.sid  )

      AND ( v$lock.id1 = obj.object_id (+))

  -- AND ( v$session.sql_hash_value = v$sqltext.hash_value )

      AND ( "V$SESSION"."USERNAME" is not null)  ;

 

2. Lock이 걸린 사용자를 Kill

   ALTER SYSTEM KILL SESSION 'SID번호, SERIAL#번호'   ;