oracle erp卡住,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;