系统难免有阻塞的情况,
遇上阻塞的话,怎么查阻塞的进程,怎么解决?
解决方案 »
- 在eclipse中连接oracle怎么老是报“The Network Adapter could not establish the connection”
- 紧急求救 ora-04030
- a表更新B表
- 求助:关于动态组合oracle存储过程参数
- oracle imp 错误942
- 如何从oracle92转到oracle817
- 奇怪错误!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 如何在ORACLE 9中提取数据库?
- 求助:处理大数据量越来越慢,不知道是数据库端的瓶颈还是应用程序端瓶颈?
- java连接linux下的oracle出错!
- 请帮我实现这种功能,统计记录条数
- 怎样避免如下SQL语句中的全表扫描?
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 条件取掉的话查询结果就像上面那样了 其实这个时候系统中是没有阻塞的 呵呵
都是些系统进程