--模拟死锁
/*
查锁和杀锁，使用
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
*/
set linesize 200;
SELECT T2.USERNAME,
T2.SID,
T2.SERIAL#,
T2.LOGON_TIME,
T3.SQL_TEXT
FROM V$LOCKED_OBJECT T1,V$SESSION T2,V$SQLTEXT T3
WHERE T1.SESSION_ID=T2.SID AND T2.SQL_ADDRESS=T3.ADDRESS
ORDER BY T2.LOGON_TIME;
ALTER SYSTEM KILL SESSION '
SID,SERIAL#';
--实例程序的执行结果如下：
SQL> set linesize 200;
SQL> SELECT T2.USERNAME,
2 T2.SID,
3 T2.SERIAL#,
4 T2.LOGON_TIME,
5 T3.SQL_TEXT
6 FROM V$LOCKED_OBJECT T1,V$SESSION T2,V$SQLTEXT T3
7 WHERE T1.SESSION_ID=T2.SID AND T2.SQL_ADDRESS=T3.ADDRESS
8 ORDER BY T2.LOGON_TIME;
USERNAME SID SERIAL# LOGON_TIME SQL_TEXT
------------------------------ ---------- ---------- -------------- --------------------------------
XAXNB 137 481 17-4月 -11 update emp set empname='
chenzzaz' where empid=2
SQL> alter system kill session '
137,481';
系统已更改
--对应产生死锁的会话
SQL> update emp set empname='
chenzzaz' where empid=2;
update emp set empname='
chenzzaz' where empid=2
*
第 1 行出现错误:
ORA-00028: 您的会话己被终止
/*20110418陈字文宝鸡项目死锁增加*/
SELECT T2.SID,
T2.SERIAL#
FROM V$LOCKED_OBJECT T1,V$SESSION T2,V$SQLTEXT T3
WHERE T1.SESSION_ID=T2.SID AND T2.SQL_ADDRESS=T3.ADDRESS
GROUP BY T2.SID,T2.SERIAL#;
ALTER SYSTEM KILL SESSION '
493,4';
ALTER SYSTEM KILL SESSION '
470,4';
ALTER SYSTEM KILL SESSION '
510,4';
ALTER SYSTEM KILL SESSION '
471,3';
