Oracle数据库死锁检测以及处理方式

首先先确保账号有对应的kill权限以及查看权限。

-- 查看死锁ID 
select username,lockwait,status,machine,program from v$session where sid 
in (select session_id from v$locked_object);

/*查看死锁语句*/
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

-- 1. 查询死锁sql:

SELECT object_name, machine, s.sid, s.serial# 
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id　= o.object_id
AND l.session_id = s.sid;

-- 2. 释放死锁

-- alter system kill session '51,585';

--  3.上方命令不行换OS杀死进程
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=10;

-- 1)在Linux上，用root身份执行命令:
-- ps -ef|grep spid
-- #kill -9 12345（即第3步查询出的spid）