The trigger may be locked; how to define it?

Question

Answering Can I skip any changes if I replace the oracle trigger while my application is running? I went to see if the trigger was blocked by an INSERT expression. This is not the case, and I cannot find anything on the Internet to suggest that the trigger can be blocked.

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 the second session try to figure out which 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 blocked.

However, if I try to replace a trigger while the INSERT statement is executing, it will not be replaced until the statement completes (not including a commit), which means that the trigger is locked.

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

Answer 1

To determine if a trigger is locked (like any other stored procedure), a dynamic performance representation of V $ ACCESS may be requested.

 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 

These locks are cache caching (library cache locks - resource locks ( TM lock locks)) necessary to ensure that the object is protected from being changed during the session.

 --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 see that session # 441 is waiting for the log file to switch, while session # 24 is waiting for the library cache to be displayed.