检查产生死锁的机器:
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))
查看死锁的进程:
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
然后alter system kill session 'sid,serial#';
select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
找到spid,spid就是oracle对应在操作系统的进程号
kill -9 spid
这样就可以解除死锁。
转载于:https://blog.51cto.com/yuangeqingtian/1333684