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

在回答 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语句运行时尝试替换触发器,则在语句完成(不包括提交)之后将不会替换它,这意味着触发器已被锁定.

在这种情况下,触发器是否被锁定,如果是,那么如何判断它是什么？

解决方法

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等待库缓存引脚.

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!