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.

3 comments:

  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.

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

    ReplyDelete
  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)

    ReplyDelete