如何查询oracle死锁,linux下查看oracle死锁、解锁

近来经常出现数据库死锁导致数据库操作时出现问题，临时在linux系统下封装了两个命令，一个查看锁(lock)一个解锁命令(killlock)，规避这类问题，避免LINUX下繁琐的oracle操作。

1.查询oracle死锁脚本(定义对应的别名命令，修改.bash_profile加入 aliaslock='xxxxx.sh,xxxx.sh内容如下)

set echo off

set feedback off

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 (select a.username, a.sid, a.serial#,　　 a.lockwait, a.machine,a.status,　　 a.last_call_et,a.sql_hash_value,a.program　　 from v$session a where nvl(a.username,'NULL')< >'NULL') a, (select id1, kaddr, sid, request,type from v$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 (select a.username, a.sid, a.serial#,　　 a.lockwait, a.machine,a.status,　　 a.last_call_et,a.sql_hash_value,a.program　　 from v$session a where nvl(a.username,'NULL')< >'NULL') a, (select id1, kaddr, sid, request,type from v$lock) b　　 where b.id1 in　　 (select distinct e.id1　　 from (select a.username, a.sid, a.serial#,　　 a.lockwait, a.machine,a.status,　　 a.last_call_et,a.sql_hash_value,a.program　　 from v$session a where nvl(a.username,'NULL')< >'NULL') d, (select id1, kaddr, sid, request,type from v$lock) e　　 where d.lockwait = e.kaddr)　　 and a.sid = b.sid　　 and b.request=0;

prompt "查出其 sql "

select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text 阻塞信息,

'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text 等待信息

from v$lock lb,

v$lock lw,

v$session sb,

v$session sw,

v$sql qb,

v$sql qw

where lb.sid=sb.sid

and lw.sid=sw.sid

and sb.prev_sql_addr=qb.address

and sw.sql_address=qw.address

and lb.id1=lw.id1

and sw.lockwait is not null

and sb.lockwait is null

and lb.block=1 ;

EXIT

2.解锁(定义对应的别名命令，修改.bash_profile加入 alias killlock='yyyy.sh,yyyy.sh内容如下)

lock_ps:

SELECT p.spid FROM   gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND' AND s.sid in (select DISTINCT t1.SID from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID);

exit

yyyy.sh

#杀OS进程方式解锁，不会出现解不掉的情况

for loop in `sqlplus***/*** @/home/***/lock_ps|grep '^[0-9]'`

do

kill -9 $loop

done