如何查看死锁时在执行的SQL语句

脚本一，查出锁的关系，谁把谁锁主了51Testing软件测试网(H\e%SU;pBCM
51Testing软件测试网4c3r rD6W6j+M{ S-z
set serveroutput on51Testing软件测试网7s,QM;S V}H
declare51Testing软件测试网b)L;xd)Ukw
mes varchar2(2000);51Testing软件测试网U?r
w[W c(M/co
tmp_id1 number(22);51Testing软件测试网k-E+`6o't5X$B9b
tmp_id2 number(22);
S5ru7l1h
AC0lk_sid    number(22);
8f5xnp8E&`"zg3M|0tmp_mac varchar2(64);51Testing软件测试网7[Cf*yrv)D/j*~
fl boolean;
$Q6sZl8@0begin
3\$ieJ;Ul0fl:=false;
1Y w F(Hm&s6u0for c1 in (select sid,serial#,machine,lockwait
:K
qN*?.f c0                  from v$session51Testing软件测试网"m7VQL$s#Qg
                  where lockwait is not null) loop51Testing软件测试网(]'B9A z:^4j`U;\
  fl:=true;
0X[4_X-s0  mes:='sid:'||to_char(c1.sid)||','||c1.machine||'is be locked by sid ';
%FW(orkkO0  select id1,id2 into tmp_id1,tmp_id2
3o#] D#WN9X&Ox(R0    from v$lock where sid=c1.sid and kaddr=c1.lockwait;51Testing软件测试网:ht(zD+E
  for c2 in (select sid    lk_sid   from  v$lock where sid!=c1.sid and id1=tmp_id1 and  id2=tmp_id2 and block='1') loop
!g a6x5m9Qn;f0   select machine into tmp_mac from v$session where sid=c2.lk_sid;
V w3KOD(H$U\0   dbms_output.put_line(mes||to_char(c2.lk_sid)||' '||tmp_mac);51Testing软件测试网o9B!Z^7BoW$S/B|
  end loop;
c+pK,Fkx0?){Xm%H0end loop;
'ACRfD,Xx"P0if not fl then51Testing软件测试网y?.OY;D+P T/js
    dbms_output.put_line('目前系统中没有互锁现象');
+d&_1Kwqt0end if;51Testing软件测试网hd wGq
h2C
aX&_
end;
7rd#fj)L0/51Testing软件测试网F6Q`F(o1Y
51Testing软件测试网"w1G[8T.f;aM"hg
第二，相应的sid最近执行的sql51Testing软件测试网x'R&TKS z*NV*`B

$]1j%`y4R*IsE0set verify off51Testing软件测试网3\)t/Ozg/Bdu7N
column a.username format a10
vQ'^;?_4o"o!A b@S0column c.sql_text format a64
kw!j)x&d,`Q0accept sid prompt 'Please enter the sid '
U7{X7p,hU:QW0select a.username, c.sql_text51Testing软件测试网1Q0Z7V IUR"t"R
from v$session a, v$sqltext c
VT!T[ z _C0where a.sid=&sid and
s5N(T.ER0      c.hash_value = a.sql_hash_value and
$N y%u9t)H)T ^!z0      c.address = a.sql_address51Testing软件测试网0R#iF"J4k5\3[)OUhb
order by c.PIECE
2M`$ca'Y
DQ0/51Testing软件测试网gw:}S$y*W R/P)]

6YI'i8E jh0首先查看alert.log文件，查询ora-60错误，其中指明了详细日志文件的路径。51Testing软件测试网WSJR!p
alert.log在background_dump_dest参数指定的目录下
C7y DS"Z0deadlock日志文件在user_dump_dest参数指定的目录下51Testing软件测试网2?c:~Q![!~R]Ux

-t&i1L6FR5\2`RO0
?K7\ E'W0@8@ XqE0select username,default_tablespace from user_users;51Testing软件测试网TH.iH0zFG#@
select * from user_role_privs;
Qj `Dw8I3i)[0select * from user_sys_privs;51Testing软件测试网+`)UD
X?s
Y/X3P
select * from user_tab_privs;51Testing软件测试网p"C%{V/D
select * from user_tables;
`TD IO3`%i'P.lr0select object_name,object_id from user_objects51Testing软件测试网?(O1x$gU
                where instr(object_name,'LOG')>0;51Testing软件测试网%G&p@!X*[ ]
select object_name,created from user_objects where object_name=upper51Testing软件测试网lc ^{N(L;F1v{:C4e

)n8Zi8H6dl2k0('&table_name');
THeqP#z2ym!qaXf0select * from user_sequences;51Testing软件测试网|/|3i
~aw:Y[
select view_name from user_views;51Testing软件测试网p;lLY$piS!N$r
select member from v$logfile;
*jf}:_R5c0Select version FROM Product_component_version51Testing软件测试网I{3URk
    Where SUBSTR(PRODUCT,1,6)='Oracle';
(A\
J;F4C[0用系统管理员，查看当前数据库有几个用户连接：51Testing软件测试网J-yq\
WCC:J;f

SQL> select username,sid,serial# from v$session;51Testing软件测试网3@)y8X/\{

如果要停某个连接用

W'@'@7Ij_Q,r0SQL> alter system kill session 'sid,serial#';51Testing软件测试网z!V|#d-l C]Z

如果这命令不行,找它UNIX的进程数

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=2151Testing软件测试网L+S,DT!\+T

2Xgb%C\;O!{0and ses.paddr=pro.addr;51Testing软件测试网7Ih%y,yNL1R

L(_K1sN?x0说明：21是某个连接的sid数51Testing软件测试网d`m{r?o6j

然后用 kill 命令杀此进程号。

!yB k+d6~*Y&w fh0select column_name,comments from dict_columns where51Testing软件测试网'n/\u b,\

table_name='USER_INDEXES';51Testing软件测试网2s0~0j8h4NQoI

|*I&f)]"C0n0Hq0select column_name,comments from dict_columns where upper(table_name)51Testing软件测试网3i
L~)a-`U$t#~;YQc

='v$session';51Testing软件测试网$tY1KNVY
如何查看最大会话数？51Testing软件测试网8t d.o~x6BngR
SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%'；
X6u#N@A&cv W0Select * from v$license;51Testing软件测试网UxTXA
SELECT *  FROM  USER_ERRORS;51Testing软件测试网| ~ To;Q5^
SELECT * FROM V$INSTANCE;51Testing软件测试网 zk,PN6b9{w
select a.sid,spid,status,substr(a.program,1,40)51Testing软件测试网q}*O~Ft/Vp;J

_(JyH!@r+v:F0prog,a.terminal,osuser,value/60/100 value
"_7vIX*C3r0     from v$session a,v$process b,v$sesstat c51Testing软件测试网
s?!hH,Q8N s*[fF
     where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by

value desc;51Testing软件测试网G0y9d`!]'qGn

select * from v$sqltext where address = 'C0000001E0B4A160';51Testing软件测试网 ~D8Iw2C`HD^
N
select sql_text, piece, hash_value51Testing软件测试网&p6@:u-~R*~a8s
from v$sqltext
'w,Ci@gc0where  address = 'C0000001E0B4A160'51Testing软件测试网^&d!e.X,S
order by hash_value, piece;51Testing软件测试网Q2x7E3c*DOV

select * from v$sqlarea where address = (select sql_address from

v$session where sid = :1);