oracle 杀死所有死锁,oracle死锁进程及杀死死锁进程

--锁对象查找

SELECT * FROM V$LOCKED_OBJECT;

--找到对应会话

SELECT * FROM V$SESSION WHERE SID=？;

--根据对应SQL_HASH_VALUE查找对应的锁语句

SELECT * FROM V$SQLTEXT WHERE HASH_VALUE=? ORDER BY PIECE ASC;

--查询锁语句及执行动作、KILL session语句

SELECT l.INST_ID,

S.SID,

S.SERIAL# ,

S.MACHINE,

O.OBJECT_NAME,

L.ORACLE_USERNAME,

L.LOCKED_MODE,

'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' ||

S.SERIAL# || '''' COMMAND,

t.SQL_TEXT

FROM gv$LOCKED_OBJECT L, gv$SESSION S, ALL_OBJECTS O, gv$sqltext t

WHERE L.SESSION_ID = S.SID

AND L.OBJECT_ID = O.OBJECT_ID

and s.sql_hash_value=t.hash_value

order by t.sql_id,t.piece ;

----查找锁对象、SESSION 以及操作系统进程ID

select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号

from gv$locked_object l , dba_objects o , gv$session s , gv$process p where l.object_id=o.object_id

and l.session_id=s.sid and s.paddr=p.addr;

--杀死进程

ALTER SYSTEM KILL SESSION '1294,35828';