宋泽江,日记500,鞋子加盟
rac环境下的阻塞不同于单实例情形，因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session，v$lock相应的应变化为全局范围来查找。本文提供了2个查询脚本，并给出实例演示那些session为阻塞者，哪些为被阻塞者。有关阻塞的概念以及单实例环境下的阻塞请参考：oracle 阻塞(blocking blocked)
1、演示环境
scott@devdb> select * from v$version where rownum<2; banner -------------------------------------------------------------------------------- oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production --在scott session中发布sql语句，并未提交 scott@devdb> begin 2 update emp set sal=sal+100 where empno=7788; 3 update dept set dname='dba' where deptno=10; 4 end; 5 / pl/sql procedure successfully completed. --在leshami session中更新emp对象 leshami@devdb> update scott.emp set sal=sal-200 where empno=7788; --在usr1 session中更新emp对象 usr1@devdb> update scott.dept set dname='dev' where deptno=10;
2、寻找阻塞
scott@devdb> @block_session_rac user_status sid_serial conn_instance sid program osuser machine lock_type lock_mode ctime object_name --------------- --------------- ---------------- ---- ------------------------------ ------- --------------- --------------- ----------- ---------- ------------------------- blocking -> '20,1545' devdb1 20 sqlplus@linux-01 (tns v1-v3) oracle linux-01 transaction exclusive 666 dept blocking -> '20,1545' devdb1 20 sqlplus@linux-01 (tns v1-v3) oracle linux-01 transaction exclusive 666 emp waiting '49,1007' devdb1 49 sqlplus@linux-01 (tns v1-v3) oracle linux-01 transaction none 618 emp waiting '933,11691' devdb2 933 sqlplus@linux-02 (tns v1-v3) oracle linux-02 transaction none 558 dept --通过上述脚本我们可以看到session '20,1545' 锁住了对象dept以及emp，而此时session '49,1007'与'933,11691'处于等待状态。 --下面是另外的一种方式来获取阻塞的情形 scott@devdb> @block_session_rac2 blocking_status ---------------------------------------------------------------------------------------------------------------------------- scott@linux-01 ( inst=1 sid=20 serail#=1545 ) is blocking usr1@linux-02 ( inst=2 sid=933 serial#=11691 ) scott@linux-01 ( inst=1 sid=20 serail#=1545 ) is blocking leshami@linux-01 ( inst=1 sid=49 serial#=1007 ) --author : leshami --blog : http://blog.csdn.net/leshami
3、演示中用到的脚本
我要评论
[oracle@linux-01 ~]$ more block_session_rac.sql
set linesize 180
col user_status format a15
col sid_serial format a15
col program format a30 wrapped
col machine format a15 wrapped
col osuser format a15 wrapped
col conn_instance format a15
col object_name format a25 wrapped
select decode (l.block, 0, 'waiting', 'blocking ->') user_status,
chr (39) || s.sid || ',' || s.serial# || chr (39) sid_serial,
(select instance_name
from gv$instance
where inst_id = l.inst_id)
conn_instance,
s.sid,
s.program,
s.osuser,
s.machine,
decode (l.type,
'rt', 'redo log buffer',
'td', 'dictionary',
'tm', 'dml',
'ts', 'temp segments',
'tx', 'transaction',
'ul', 'user',
'rw', 'row wait',
l.type)
lock_type--,id1
--,id2
,
decode (l.lmode,
0, 'none',
1, 'null',
2, 'row share',
3, 'row excl.',
4, 'share',
5, 's/row excl.',
6, 'exclusive',
ltrim (to_char (lmode, '990')))
lock_mode,
ctime--,decode(l.block, 0, 'not blocking', 1, 'blocking', 2, 'global') lock_status
,
object_name
from gv$lock l
join gv$session s on (l.inst_id = s.inst_id and l.sid = s.sid)
join gv$locked_object o
on (o.inst_id = s.inst_id and s.sid = o.session_id)
join dba_objects d on (d.object_id = o.object_id)
where (l.id1, l.id2, l.type) in (select id1, id2, type
from gv$lock
where request > 0)
order by id1, id2, ctime desc;
[oracle@linux-01 ~]$ more block_session_rac2.sql
select distinct
s1.username
|| '@'
|| s1.machine
|| ' ( inst='
|| s1.inst_id
|| ' sid='
|| s1.sid
|| ' serail#='
|| s1.serial#
|| ' ) is blocking '
|| s2.username
|| '@'
|| s2.machine
|| ' ( inst='
|| s2.inst_id
|| ' sid='
|| s2.sid
|| ' serial#='
|| s2.serial#
|| ' ) '
as blocking_status
from gv$lock l1,
gv$session s1,
gv$lock l2,
gv$session s2
where s1.sid = l1.sid
and s2.sid = l2.sid
and s1.inst_id = l1.inst_id
and s2.inst_id = l2.inst_id
and l1.block > 0
and l2.request > 0
and l1.id1 = l2.id1
and l1.id2 = l2.id2;
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
Oracle字符串函数length、upper、lower、initcap、 concat、instr、replace
网友评论