查看哪些表被死锁:
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a, dba_objects b
where b.object_id = a.object_id;
查看数据库是否有死锁,死锁所在机器:
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
USERNAME LOCKWAIT STATUS MACHINE PROGRAM
---------- ---------------- -------- -------------------- ------------------------------------------------
LYYKT ACTIVE APPSnode01 JDBC Thin Client
Username:死锁语句所用的数据库用户;
Lockwait:死锁的状态,如果有内容表示被死锁。
Status: 状态,active表示被死锁
Machine: 死锁语句所在的机器。
Program: 产生死锁的语句主要来自哪个应用程序。
查看产生死锁的语句:
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));
-------------------
update FZX_TJXFZCLS set CLZT = :1 , QFSJ = :2 , rwh = :3 where jybz in (select * from (select jybz from FZX_TJXFZCLS where CLZT = :4 or ((QFSJ + 1/96) < :5 and CLZT = :6) order by JYSJ) where rownum <= 5000)
1)查找死锁的进程:
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;
USERNAME OBJECT_ID SESSION_ID SERIAL# ORACLE_USERNAME OS_USER_NAME PROCESS
---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------
LYYKT 78313 1570 46437 LYYKT root 1234
LYYKT 78217 2135 22199 LYYKT root 1234
2)kill掉这个死锁的进程(Oracle进程):
alter system kill session ‘sid,serial#’; (其中sid=l.session_id)
3)查找sid,然后使用系统命令kill来杀掉死锁的进程(操作系统进程)
select pro.spid from v$session ses,v$process pro where ses.sid=2135 and ses.paddr=pro.addr;
1507——spid:4401
2135——spid:6925
ps -ef|grep spid 其中spid是这个进程的进程号,kill掉这个Oracle进程。
kill -9 进程号
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a, dba_objects b
where b.object_id = a.object_id;
查看数据库是否有死锁,死锁所在机器:
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
USERNAME LOCKWAIT STATUS MACHINE PROGRAM
---------- ---------------- -------- -------------------- ------------------------------------------------
LYYKT ACTIVE APPSnode01 JDBC Thin Client
Username:死锁语句所用的数据库用户;
Lockwait:死锁的状态,如果有内容表示被死锁。
Status: 状态,active表示被死锁
Machine: 死锁语句所在的机器。
Program: 产生死锁的语句主要来自哪个应用程序。
查看产生死锁的语句:
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));
-------------------
update FZX_TJXFZCLS set CLZT = :1 , QFSJ = :2 , rwh = :3 where jybz in (select * from (select jybz from FZX_TJXFZCLS where CLZT = :4 or ((QFSJ + 1/96) < :5 and CLZT = :6) order by JYSJ) where rownum <= 5000)
1)查找死锁的进程:
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;
USERNAME OBJECT_ID SESSION_ID SERIAL# ORACLE_USERNAME OS_USER_NAME PROCESS
---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------
LYYKT 78313 1570 46437 LYYKT root 1234
LYYKT 78217 2135 22199 LYYKT root 1234
2)kill掉这个死锁的进程(Oracle进程):
alter system kill session ‘sid,serial#’; (其中sid=l.session_id)
3)查找sid,然后使用系统命令kill来杀掉死锁的进程(操作系统进程)
select pro.spid from v$session ses,v$process pro where ses.sid=2135 and ses.paddr=pro.addr;
1507——spid:4401
2135——spid:6925
ps -ef|grep spid 其中spid是这个进程的进程号,kill掉这个Oracle进程。
kill -9 进程号
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29101923/viewspace-1450409/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29101923/viewspace-1450409/