oracle – 可以锁定触发器;怎么会确定它是？

概述在回答 Will I miss any changes if I replace an oracle trigger while my application is running?时,我去查看触发器是否被INSERT语句锁定.它不是,我在互联网上找不到任何建议可以锁定触发器的东西. 如果我在一个会话中运行以下内容： create table test_trigger (id number);c 在回答 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 rowbegin  insert into test_trigger_h (ID) values (:new.ID);end;    /insert into test_trigger select level   from dualconnect 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 #1insert into test_trigger select level   from dualconnect by level <= 1000000; Session #2sql> 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 # 441insert into test_trigger  select level    from dual connect by level <= 1000000;-- session sID #24create or replace trigger test_trigger_t after insert on test_trigger for each rowbegin  insert into test_trigger_h (ID) values (:new.ID);end;  -- Session # 3select 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等待库缓存引脚.

总结

以上是内存溢出为你收集整理的oracle – 可以锁定触发器;怎么会确定它是？全部内容，希望文章能够帮你解决oracle – 可以锁定触发器;怎么会确定它是？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。