触发器可以锁定吗?如何确定它是?

本文介绍了触发器可以锁定吗?如何确定它是?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在回答在我的应用程序运行时替换oracle触发器，我会错过任何更改吗?，我一直在寻找查看触发器是否已被INSERT语句锁定.不是，我在互联网上找不到任何可以触发的东西.

In answering Will I miss any changes if I replace an oracle trigger while my application is running?, I went looking to see if the trigger was locked by an INSERT statement. It wasn't and I can't find anything on the internet to suggest that a trigger can be locked.

如果我在一个会话中运行以下命令:

If I run the following in one session:

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;

然后在第二个会话中尝试找出正在发生的锁定，我得到以下信息:

and then in a second session try to find out what locks are occurring I get the following:

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，触发器是 not 被锁定.

According to Oracle, the trigger is not being locked.

但是，如果我尝试在INSERT语句运行时替换触发器，则直到该语句完成(不包括提交)后才替换触发器，这意味着触发器 已锁定

However, if I try to replace the trigger whilst the INSERT statement is running it will not be replaced until after the statement has completed (not including a commit), which implies that the trigger is locked.

在这种情况下，触发器是否已锁定?如果是，则如何确定触发器是

In this situation, is the trigger locked and if so how would one determine that it is?

推荐答案

要确定是否已锁定触发器(以及任何其他存储过程)，请使用 V $ ACCESS 动态性能视图.

To determine if a trigger(as well as any other stored procedure) is locked or not, the V$ACCESS dynamic performance view can be queried.

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锁类型)锁)，需要确保在执行会话时防止对象被修改.它.

Those kinds of locks are library cache pins(library cache locks are resource(TM type of lock) locks), needed to ensure that an object is protected from being modified while session is executing it.

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

Here we can see that the session #441 waits for a log file switching and session #24 waits for library cache pin.