I encountered an interesting scenario recently… a “mutating table” error but the reported table had no trigger on it.
The Oracle documentation has this to say about ORA-04091:
ORA-04091: table string.string is mutating, trigger/function may not see it Cause: A trigger (or a user defined PL/SQL function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. Action: Rewrite the trigger (or function) so it does not read that table.
Basically, if we have a row level trigger on a table then our trigger cannot read or write the same table that the trigger belongs to. The following illustrates this:
SQL> create table src_tab
2 (id number(10) not null
3 ,val char(10) not null
4 ,constraint src_tab_pk primary key (id));
Table created.
SQL> insert /*+ append */ into src_tab
2 (id, val)
3 with row_gen as (select rownum as rn from dual connect by level <= 1000)
4 select rownum
5 , 'x'
6 from row_gen r1
7 , row_gen r2;
1000000 rows created.
SQL> commit;
SQL> create or replace trigger src_tab_aiu
2 after insert or update
3 on src_tab
4 for each row
5 declare
6 l_temp number;
7 begin
8
9 select count(*)
10 into l_temp
11 from src_tab;
12
13 end src_tab_aiu;
14 /
Trigger created.
SQL> update src_tab
2 set val = 'y'
3 where id = 1234;
update src_tab
*
ERROR at line 1:
ORA-04091: table DEVELOPER.SRC_TAB is mutating, trigger/function may not see it
ORA-06512: at "DEVELOPER.SRC_TAB_AIU", line 5
ORA-04088: error during execution of trigger 'DEVELOPER.SRC_TAB_AIU'
In the above we created a table, populated it with a million rows, added a row level trigger that counts the number of rows in the table and then attempted to update a row. The update caused the trigger to fire, which then failed with our ORA-04091 mutating table error.
The scenario I encountered recently reported a valid trigger in the system but the table noted from the error was not the table upon which the trigger was defined. The trigger in question did insert into the reported table and had been operating successfully for a number of years so the usual cause of the ORA-04091 exception was puzzling…
… until one of the DBAs reported that they had been doing some online table maintenance. The cause of the ORA-04091 scenario was then easy to replicate.
We’ll start with a source table and define a trigger that inserts the data changes into a log table, mimicking what might be an audit or change capture process. We’ll then run a series of updates against the source table and at the same time perform an online move of the log table (this was run on a 21c Express Edition database):
SQL> create table src_tab 2 (id number(10) not null 3 ,val char(10) not null 4 ,constraint src_tab_pk primary key (id)); Table created. SQL> insert /*+ append */ into src_tab 2 (id, val) 3 with row_gen as (select rownum as rn from dual connect by level <= 1000) 4 select rownum 5 , 'x' 6 from row_gen r1 7 , row_gen r2; 1000000 rows created. SQL> commit; Commit complete. SQL> create table src_tab_log 2 (id number(10) not null 3 ,val char(10) not null 4 ,update_timestamp timestamp not null); Table created. SQL> insert /*+ append */ into src_tab_log 2 (id, val, update_timestamp) 3 with row_gen as (select rownum as rn from dual connect by level <= 1000) 4 select rownum 5 , 'x' 6 , systimestamp 7 from row_gen r1 8 , row_gen r2; 1000000 rows created. SQL> commit; Commit complete. SQL> create or replace trigger src_tab_aiu 2 after insert or update 3 on src_tab 4 for each row 5 declare 6 begin 7 8 insert into src_tab_log 9 (id 10 ,val 11 ,update_timestamp) 12 values 13 (:NEW.id 14 ,:NEW.val 15 ,systimestamp); 16 17 dbms_session.sleep (0.01); -- just to slow down the processing 18 19 end src_tab_aiu; 20 / Trigger created. SQL> update src_tab -- to show that the trigger does work 2 set val = 'y' 3 where id = 1234; 1 row updated. SQL> commit; Commit complete. SQL> begin -- run lots of updates and in another session run an online move of src_tab_log 2 for i in 1..1000 3 loop 4 update src_tab 5 set val = 'y' 6 where id = i*100; 7 commit; 8 end loop; 9 end; 10 / begin * ERROR at line 1: ORA-04091: table DEVELOPER.SRC_TAB_LOG is mutating, trigger/function may not see it ORA-06512: at "DEVELOPER.SRC_TAB_AIU", line 4 ORA-04088: error during execution of trigger 'DEVELOPER.SRC_TAB_AIU' ORA-06512: at line 4
Whilst the last block updating the table rows was running, the following was kicked off in another session:
SQL> alter table src_tab_log move online;
which caused the reported ORA-04091.
Online partitioning of a table has the same impact. It seems versions 19c and 21c are affected.
So, our online table maintenance operation can interfere with the usual operation of the database when the table is subject to DML invoked from a trigger. I’m kinda inclined to call “bug” on this but I guess Oracle might like to describe it as an “unavoidable feature”. Regardless, it’s something to be aware of and to avoid online table reorganisations on tables subject to trigger sourced DML.
Mark Hoxey 