30 March 2011

Different mutating table behavior when using SYSDBA account (2142)

The 29 March quiz focused on the fact that a mutating table error can be raised not only when a trigger is executed, but also when a function is called from within an SQL statement.

A player noticed that when you run the verification code for this quiz from a SYSDBA account, no error is raised.

I lack the time to do more experimentation; perhaps a player can see if this change in behavior extends to mutating table errors in triggers - and also uncover an explanation for this behavior.


  1. I tested this briefly and it appears that when the trigger is owned by the SYSDBA user (SYS in my case) then mutating triggers/function rules do not apply?

    Interestingly, in my testing, I also noticed another tidbit that I wasn't aware of: triggers cannot be created on objects owned by SYS (ORA-0489). So, for my test, I created a table in the SCOTT schema, but created my trigger as SYS. With this setup, I get no mutation errors whether my insert/update is done by SYS or by SCOTT. If I drop the SYS trigger though and add the same trigger as SCOTT then I get the mutation errors.

  2. Interesting observation, but ... SYS should nver, ever be used for such purposes.
    As Tom Kyte like to put it "SYS is different".

  3. A trigger owned by SYS appears resistant to the mutating table error. If I had to guess, I'd say that it is because SYS needs some leeway to cater for recursive SQL (eg adding partitions when inserting rows)