可以锁定触发器;(Can a trigger be locked; how would one determine that it is?)

 在回答中如果我在应用程序运行时替换oracle触发器，我会错过任何更改吗？ ，我去查看触发器是否被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语句运行时尝试替换触发器，则在语句完成（不包括提交）之后将不会替换它，这意味着触发器已被锁定。 
 
 在这种情况下，触发器是否被锁定，如果是，那么如何判断它是什么？ 

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)
 
According to Oracle, the trigger is not being locked.
 
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?

原文：https://stackoverflow.com/questions/18460260