Can I skip any changes if I replace the oracle trigger while my application is running?

Question

I am wondering if I will be skipping any data if I replace the trigger while my oracle database is being used. I created a toy example, and it looks like I won’t do it, but one of my colleagues says otherwise.

create table test_trigger (id number); create table test_trigger_h (id number); create sequence test_trigger_seq; --/ 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; / --/ begin for i in 1..100000 loop insert into test_trigger (id) values (test_trigger_seq.nextval); end loop; end; / --/ begin for i in 1..10000 loop execute immediate '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;'; end loop; end; / ran the two loops at the same time select count(1) from test_trigger; COUNT(1) 100000 select count(1) from test_trigger_h; COUNT(1) 100000 

Answer 1

create or replace - lock the table. Thus, all inserts will wait until they are finished. Do not worry about missing inserts.

Answer 2

I think you might be going to check it out wrong. Your insert instructions will not take time, and therefore replacing a trigger may fit in between the inserts. At least this is what I consider due to below.

If you change your test to make sure you have a long SQL statement, for example

 create table test_trigger (id number); create table test_trigger_h (id number); create sequence test_trigger_seq; 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; 

If you then try to replace the trigger in a separate session, this will only happen after the insert is completed.

Unfortunately, I cannot find anything in the documentation to support me; this is just the behavior that I know of.

Answer 3

After responding to the URL, that trigger can be changed while the application is running . it will be blocked by the "library cache" and NOT "block data". Oracle processes it internally without worrying about it.

Check the question raised by Ben- Is it possible to lock the trigger; How to determine what it is?

- Run this from session 2: select * from v $ access, where object = upper ('test_trigger_t');