Trigger issue: Table is mutating (ora-04091)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Trigger issue: Table is mutating (ora-04091)

  1. #1
    Join Date
    Oct 2006
    Posts
    175

    Trigger issue: Table is mutating (ora-04091)

    Hi all,
    I have a trigger(say Trg1) that is called after insert or update on a table (say Tbl1). This trigger in turn calls a procedure (say Proc1) from another schema, which does a select on the Tbl1. I can understand this is a mutant situation(ora-04091). But what is amazing is this trigger works when I put that Proc1 in sys schema. But it just won't work if I put this procedure anywhere else. Has anybody experienced this issue before? Any suggestions, advice is highly appreciated.

    Thanks
    gtcol

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Table mutating syndrome is caused by a trigger hitting the same table where the trigger is sitting. As per your scenario description the known cause of the syndrome applies.

    Solution is for the trigger not to shoot its own feet.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2006
    Posts
    175
    Actually, what I'm trying to do now is move all procedures in sys schema to some other schema for security reasons, but this trigger just won't work when I do so, where as it was working just fine when the procedure was in sys schema. .

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I would rework the design... trigger hitting its own base table is a call for troubles; just doesn't work, check Metalink.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Quote Originally Posted by gtcol View Post
    Actually, what I'm trying to do now is move all procedures in sys schema to some other schema for security reasons, but this trigger just won't work when I do so, where as it was working just fine when the procedure was in sys schema. .
    You should not store anything in the sys schema. It will end up making your database much less safe as you will probably do grants to the schema users to use your code, you might have a conflict with future Oracle supplied objects and it is just a really bad idea.

    While triggers are not bad in and of themselves, you should think long and hard about any business logic that you put in a trigger, especially row level triggers. Since any inefficiency that comes up will get multiplied as you database grows preventing it from being scalable. [steps off soap box]

    What exactly are you trying to accomplish with the trigger, and can't you just pass all of the data that the procedure needs through parameters?
    this space intentionally left blank

  6. #6
    Join Date
    Oct 2006
    Posts
    175
    Thanks gandolf989, PAVB.
    Well we're trying to move objects out of sys schema to some other user schema.
    About passing data, we could do it through parameters, if only it always were one row. It can be multiple rows at times. The procedure does a merge with other tables (this is the root cause of mutating table).

    Actually, what my head is spinning over is why it works when the procedure is in sys schema and doesn't, when its in user schemas? I have given all possible, even sysdba and dba roles, to that user? Still all in vain. What roles are required here, for that user? i am confused.

  7. #7
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    I ** never ** created anything inside SYS, this REALLY is like shooting yourself, but your answer is, imho : SYS is special, SYS is the owner of internal tables, SYS bypass a lot of features (like table export, fga, etc), AND sys is supposed to be used ONLY BY THE ORACLE DATABASE itself, so is reasonable suppose that the check for mutating tables is less rigorous (or even is disabled) to the SYS schema , so your trigger "works" inside the ultra special internal and "magic" schema SYS but not any other, being the other SYSDBA or whatever...
    Anyway, your job is clear : clean the mess, move your triggers/programs/objects OUT of SYS, and correct the BUG of referencing in your code the same table used in the trigger ... Search in this same forum, you will find some alternatives to rewrite your code.

    regards,

    Chiappa

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    Quote Originally Posted by gtcol View Post
    Thanks gandolf989, PAVB.
    Well we're trying to move objects out of sys schema to some other user schema.
    About passing data, we could do it through parameters, if only it always were one row. It can be multiple rows at times. The procedure does a merge with other tables (this is the root cause of mutating table).

    Actually, what my head is spinning over is why it works when the procedure is in sys schema and doesn't, when its in user schemas? I have given all possible, even sysdba and dba roles, to that user? Still all in vain. What roles are required here, for that user? i am confused.
    So I have not tried to create triggers in sys, but I suppose it is possible that triggers in sys do not throw the mutating table error, hence it might work in sys but not anywhere else.

    Mutating errors and deadlocks almost always fall into the category of bad application design. Instead of just trying to make this work you need to look at the application with a more holistic view. The real answer may be redefining tables and how they are related in your schema. IMHO, the best answer is not just one that works, but works simply and is easy to maintain.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width