电子商务做网站实训体会/嘉兴seo外包平台
近来经常出现数据库死锁导致数据库操作时出现问题，临时在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