ORACLE ERP解决死锁的方案

ORACLE ERP系统中会经常发生死锁的问题。很多用户会说某个表被锁住了，要求DBA解锁。其实解锁就是看哪些SESSION锁住了资源，而这些资源恰恰是其它SESSION需要或自己需要的。这时需要将这个SESSION KILL掉.KILL这个SESSION的时候最好和业务人员作确认!很多文章介绍了查看当前数据库里锁的情况可以用这样一个SQL语句查看：

col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id
--其实这个语句很耗时，不建议使用。这个语句把系统所有锁定对象都列出来了，没有必要。

 

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

如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁。其实这个也是不可取的。

 

alter system kill session 'sid,serial#'

 

以下的SQL脚本可以很有效率的找到ERP中的锁并将SEESION KILL之。我已经在ERP生产系统中得到验证！

 

set echo on
set feedback on

prompt '删除旧数据.....'
truncate table sjhdba_session;
truncate table sjhdba_lock;
truncate table sjhdba_sqltext;

 

prompt '获得新数据.....'
insert into sjhdba_session
select a.username, a.sid, a.serial#,
a.lockwait, a.machine,a.status,a.action,
a.last_call_et,a.sql_hash_value,a.program
from v$session a
where username is not null;

insert into sjhdba_lock
select id1, kaddr, sid, request,type
from v$lock;

insert into sjhdba_sqltext
select hash_value , sql_text
from v$sqltext s, sjhdba_session m
where s.hash_value=m.sql_hash_value;

 

column username format a10
column machine format a15
column last_call_et format 99999 heading "Seconds"
column sid format 9999

 

prompt "正在等待别人的用户"
select a.sid, a.serial#,
a.machine,a.last_call_et, a.username, b.id1
from sjhdba_session a, sjhdba_lock b
where a.lockwait = b.kaddr;

 

prompt "被等待的用户"
select a.sid, a.serial#,
a.machine, a.last_call_et,a.username,
b.type,a.status,b.id1
from sjhdba_session a, sjhdba_lock b
where b.id1 in
(select distinct e.id1
from sjhdba_session d, sjhdba_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0;

prompt "查出其 sql "
select a.username, a.sid, a.serial#,a.action,
b.id1, b.type, c.sql_text
from sjhdba_session a, sjhdba_lock b, sjhdba_sqltext c
where b.id1 in
(select distinct e.id1
from sjhdba_session d, sjhdba_lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and b.request=0
and c.hash_value =a.sql_hash_value;