在回答
Will I miss any changes if I replace an oracle trigger while my application is running?时,我去查看触发器是否被INSERT语句锁定.它不是,我在互联网上找不到任何建议可以锁定触发器的东西.
如果我在一个会话中运行以下内容：
create table test_trigger (id number); create table test_trigger_h (id number); create or replace trigger test_trigger_t after insert on test_trigger for each row begin insert into test_trigger_h (id) values (:new.id); end; / insert into test_trigger select level from dual connect by level <= 1000000;
然后在第二个会话中尝试找出正在发生的锁定我得到以下内容：
select object_name,object_type,case l.block
when 0 then 'Not Blocking'
when 1 then 'Blocking'
when 2 then 'Global'
end as status,case v.locked_mode
when 0 then 'None'
when 1 then 'Null'
when 2 then 'Row-S (SS)'
when 3 then 'Row-X (SX)'
when 4 then 'Share'
when 5 then 'S/Row-X (SSX)'
when 6 then 'Exclusive'
else to_char(lmode)
end as mode_held
from v$locked_object v
join dba_objects d
on v.object_id = d.object_id
join v$lock l
on v.object_id = l.id1
join v$session s
on v.session_id = s.sid
;
OBJECT_NAME OBJECT_TYPE STATUS MODE_HELD
-------------------- -------------------- --------------- ---------------
TEST_TRIGGER TABLE Not Blocking Row-X (SX)
TEST_TRIGGER_H TABLE Not Blocking Row-X (SX)
根据Oracle的说法,触发器没有被锁定.
但是,如果我在INSERT语句运行时尝试替换触发器,则在语句完成(不包括提交)之后将不会替换它,这意味着触发器已被锁定.
在这种情况下,触发器是否被锁定,如果是,那么如何判断它是什么？
解决方法
要确定是否锁定了触发器(以及任何其他存储过程),可以查询
V$ACCESS动态性能视图.
Session #1 insert into test_trigger select level from dual connect by level <= 1000000; Session #2 sql> select * 2 from v$access 3 where object = upper('test_trigger_t') 4 ; Sid Owner Object Type Con_Id -------------------------------------- 441 HR TEST_TRIGGER_T TRIGGER 3
这些类型的锁是库高速缓存引脚(库高速缓存锁是资源(TM类型的锁)锁),需要确保在会话执行时保护对象不被修改.
--session sid # 441
insert into test_trigger
select level
from dual
connect by level <= 1000000;
-- session sid #24
create or replace trigger test_trigger_t
after insert on test_trigger for each row
begin
insert into test_trigger_h (id) values (:new.id);
end;
-- Session # 3
select vs.sid,vs.username,vw.event
from v$session vs
join v$session_wait vw
on (vw.sid = vs.sid)
join v$access va
on (va.owner = vs.username)
where vs.username = 'HR'
结果：
Sid Username Event -------------------------- 24 HR library cache pin .... 441 HR log file switch (checkpoint incomplete)
在这里我们可以看到会话#441等待日志文件切换,会话#24等待库缓存引脚.
