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

-- 查看死锁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）