查询阻塞 select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid||','||d.serial# block_msg, a.block from v$lock a,v$lock b,v$session c,v$session d where a.id1=b.id1 and a.id2=b.id2 and a.block>0 and a.sid<>b.sid and a.sid=c.sid and b.sid=d.SID 如果阻塞的时间太长,可以kill掉
BLOCK NUMBER A value of either 0 or 1, depending on whether or not the lock in question is the blocker. 呵呵 你理解BLOCK这个字段吗 SQL> select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid ||','||d.serial# block_msg, a.block 2 from v$lock a,v$lock b,v$session c,v$session d 3 where a.id1=b.id1 4 and a.id2=b.id2 5 and a.sid <>b.sid 6 and a.sid=c.sid 7 and b.sid=d.SID ;BLOCK_MSG BLOCK ---------------------------------------- ---------- HWANG ('165,1') is blocking 167,1 0 HWANG ('161,1') is blocking 167,1 0 HWANG ('161,1') is blocking 167,1 0 HWANG ('161,1') is blocking 166,1 0 HWANG ('166,1') is blocking 167,1 0 HWANG ('166,1') is blocking 167,1 0 HWANG ('166,1') is blocking 161,1 0 HWANG ('167,1') is blocking 166,1 0 HWANG ('167,1') is blocking 161,1 0 HWANG ('167,1') is blocking 166,1 0 HWANG ('167,1') is blocking 161,1 0BLOCK_MSG BLOCK ---------------------------------------- ---------- HWANG ('167,1') is blocking 157,1 0 HWANG ('167,1') is blocking 165,1 0 HWANG ('157,1') is blocking 167,1 014 rows selected. SQL> select sid,serial#,username from v$session; SID SERIAL# USERNAME ---------- ---------- ------------------------------ 136 179 SYS 142 70 143 1 147 1 150 1 153 15 SCOTT 154 1 155 1 156 3 157 1 160 1 SID SERIAL# USERNAME ---------- ---------- ------------------------------ 161 1 162 1 163 1 164 1 165 1 166 1 167 1 168 1 169 1 170 121 rows selected. 我把and a.block>0 条件取掉的话查询结果就像上面那样了 其实这个时候系统中是没有阻塞的 呵呵
select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid||','||d.serial# block_msg, a.block
from v$lock a,v$lock b,v$session c,v$session d
where a.id1=b.id1
and a.id2=b.id2
and a.block>0
and a.sid<>b.sid
and a.sid=c.sid
and b.sid=d.SID
如果阻塞的时间太长,可以kill掉
NUMBER
A value of either 0 or 1, depending on whether or not the lock in question is the blocker. 呵呵 你理解BLOCK这个字段吗
SQL> select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid
||','||d.serial# block_msg, a.block
2 from v$lock a,v$lock b,v$session c,v$session d
3 where a.id1=b.id1
4 and a.id2=b.id2
5 and a.sid <>b.sid
6 and a.sid=c.sid
7 and b.sid=d.SID ;BLOCK_MSG BLOCK
---------------------------------------- ----------
HWANG ('165,1') is blocking 167,1 0
HWANG ('161,1') is blocking 167,1 0
HWANG ('161,1') is blocking 167,1 0
HWANG ('161,1') is blocking 166,1 0
HWANG ('166,1') is blocking 167,1 0
HWANG ('166,1') is blocking 167,1 0
HWANG ('166,1') is blocking 161,1 0
HWANG ('167,1') is blocking 166,1 0
HWANG ('167,1') is blocking 161,1 0
HWANG ('167,1') is blocking 166,1 0
HWANG ('167,1') is blocking 161,1 0BLOCK_MSG BLOCK
---------------------------------------- ----------
HWANG ('167,1') is blocking 157,1 0
HWANG ('167,1') is blocking 165,1 0
HWANG ('157,1') is blocking 167,1 014 rows selected.
SQL> select sid,serial#,username from v$session; SID SERIAL# USERNAME
---------- ---------- ------------------------------
136 179 SYS
142 70
143 1
147 1
150 1
153 15 SCOTT
154 1
155 1
156 3
157 1
160 1 SID SERIAL# USERNAME
---------- ---------- ------------------------------
161 1
162 1
163 1
164 1
165 1
166 1
167 1
168 1
169 1
170 121 rows selected.
我把and a.block>0 条件取掉的话查询结果就像上面那样了 其实这个时候系统中是没有阻塞的 呵呵
都是些系统进程