Can a trigger be locked; how would one determine that it is?

Question

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?

Answer 1

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 

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'

Result:

Sid Username Event 
--------------------------
24 HR library cache pin 
....
441 HR log file switch (checkpoint incomplete) 

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