select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time; --查看锁比如查到146,21177 --alter system kill session 'sid,serial#'; --把锁给KILL掉 alter system kill session '146,21177'; 如果杀不掉: select spid from v$process p ,v$session v where p.addr= v.paddr and v.sid = 146; 然后orakill ora_sid spid;
给你提供些语句,希望对你有帮助. --查看等待事件SELECT W.EVENT, S.STATUS, Q.SQL_TEXT FROM V$SESSION_WAIT W, V$SESSION S, V$SQL Q WHERE W.SID = S.SID AND Q.HASH_VALUE = S.SQL_HASH_VALUE AND W.EVENT NOT LIKE '%message from client%' AND S.SID =&yoursid ;--查看谁锁住的 select t2.username from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time --解锁 select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.object_name from v$locked_object t1,v$session t2 ,user_objects t3 where t1.session_id=t2.sid and t1.object_id = t3.object_id order by t2.logon_time
-- kill之: alter system kill session 'sid,serial#';
看oracle的alert_sid.log有什么异常没有
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
--查看锁比如查到146,21177
--alter system kill session 'sid,serial#';
--把锁给KILL掉
alter system kill session '146,21177';
如果杀不掉:
select spid from v$process p ,v$session v
where p.addr= v.paddr
and v.sid = 146;
然后orakill ora_sid spid;
--查看等待事件SELECT W.EVENT, S.STATUS, Q.SQL_TEXT
FROM V$SESSION_WAIT W, V$SESSION S, V$SQL Q
WHERE W.SID = S.SID
AND Q.HASH_VALUE = S.SQL_HASH_VALUE
AND W.EVENT NOT LIKE '%message from client%'
AND S.SID =&yoursid ;--查看谁锁住的
select t2.username
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time --解锁
select t2.username,t2.sid,t2.serial#,t2.logon_time,t3.object_name
from v$locked_object t1,v$session t2 ,user_objects t3
where t1.session_id=t2.sid and t1.object_id = t3.object_id order by t2.logon_time
-- kill之:
alter system kill session 'sid,serial#';
主要思路就是找到占用资源的session,然后分析是什么原因,如果一时解决不了,就把该会话kill;
另外还要看系统的参数是否设置的合适;
是磁盘组有个磁盘有点问题,写log的时候 LGWR 一直挂起的.
然后就是等待等待.非常的慢
DBA来帮你分析一下吧,是不是有大的任务在进行磁盘读写?
DBA知识的人
另外备份也要做好
解决了,是MIS系统前一天新发布了新程序,造成了死锁等待.找他们修改就OK了.