Mutating Table Error
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the mutating trigger in oracle 11g workings and policies of this site About Us Learn more about Stack
Mutating Trigger In Oracle 10g With Example
Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users mutating trigger with example Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in
Oracle Mutating Trigger Pragma Autonomous Transaction
the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top What are the causes and solutions for mutating table errors? up vote 7 down vote favorite 1 I understand mutating table errors are caused by a statement level trigger in oracle design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but we do not know how. What exactly causes mutating table errors and how would our DBA have fixed the problem? sql oracle trigger plsql share|improve this question edited Jun 25 '15 at 17:56 Mahi_0707 1033 asked Sep 6 '11 at 8:46 parmanand 112238 add a comment| 3 Answers 3 active oldest votes up vote 13 down vote accepted The most likely cause of a mutating table error is the misuse of triggers. Here is a typical example: you insert a row in table A a trigger on table A (for each row) executes a query on table A, for example to compute a summary column Oracle throws an ORA-04091: table A is mutating, trigger/function may not see it This is an expected and normal behaviour, Oracle wants to protect you from yourself since Oracle guarantees: (i) that each statement is atomic (i.e will either fail or succeed completely) (ii) that each statement sees a consisten
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles » 9i » Here Mutating
Pragma Autonomous_transaction In Trigger
Table Exceptions Mutating table exceptions occur when we try to reference the triggering table in
Ora-04091 Solution
a query from within row-level trigger code. In this article I'll present examples of how a mutating table exception might occur and mutating table error in oracle 11g with example simple methods to get round it. Test Schema Mutating Table Demonstration Solution 1 (Collection in Package Variable) Solution 2 (Global Temporary Table) Test Schema The following schema objects are necessary to run the code in this http://dba.stackexchange.com/questions/5432/what-are-the-causes-and-solutions-for-mutating-table-errors article. CREATE TABLE tab1 ( id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL ); ALTER TABLE tab1 ADD ( CONSTRAINT tab1_pk PRIMARY KEY (id) ); CREATE SEQUENCE tab1_seq; CREATE TABLE tab1_audit ( id NUMBER(10) NOT NULL, action VARCHAR2(10) NOT NULL, tab1_id NUMBER(10), record_count NUMBER(10), created_time TIMESTAMP ); ALTER TABLE tab1_audit ADD ( CONSTRAINT tab1_audit_pk PRIMARY KEY (id) ); ALTER TABLE tab1_audit ADD ( CONSTRAINT tab1_audit_tab1_fk FOREIGN KEY (tab1_id) REFERENCES tab1(id) ); CREATE https://oracle-base.com/articles/9i/mutating-table-exceptions SEQUENCE tab1_audit_seq; Mutating Table Demonstration Let's assume we need to audit the actions on the parent table and for some reason, this involves querying the triggering table. We can demonstrate this with the following package and trigger. We place all our trigger code into a package as follows. CREATE OR REPLACE PACKAGE trigger_api AS PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE, p_action IN VARCHAR2); END trigger_api; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY trigger_api AS PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE, p_action IN VARCHAR2) IS l_count NUMBER(10) := 0; BEGIN SELECT COUNT(*) INTO l_count FROM tab1; INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time) VALUES (tab1_audit_seq.NEXTVAL, p_action, p_id, l_count, SYSTIMESTAMP); END tab1_row_change; END trigger_api; / SHOW ERRORS Next we create the row-level trigger itself to catch any changes to the table. CREATE OR REPLACE TRIGGER tab1_ariu_trg AFTER INSERT OR UPDATE ON tab1 FOR EACH ROW BEGIN IF inserting THEN trigger_api.tab1_row_change(p_id => :new.id, p_action => 'INSERT'); ELSE trigger_api.tab1_row_change(p_id => :new.id, p_action => 'UPDATE'); END IF; END; / SHOW ERRORS If we try to insert into the TAB1 table we might expect the insert to complete and the audit record to be created but as you can see below this is not the case. SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, 'ONE'); INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL,
- 8:40 pm UTC Category: SQL*Plus � Version: 8.1.7 Latest Followup You Asked hello, i've got a table MRC and a trigger on it (AFTER INSERT) thus, after an insert in the table MRC, this trigger has https://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:9579487119866 to determine if a new line must be inserted into an other table PLAN : for that, it does compare the :new values with the MOST RECENT enregistrement of MRC but i got a mutating table http://www.databasejournal.com/features/oracle/mutating-table-errorin-oraclewhyithappensand-what-you-can-doabout-it.html error i understand the problem but how can i get over ?? thanks Arnaud and we said... My personal opinion -- when I hit a mutating table error, I've got a serious fatal flaw in mutating trigger my logic. Have you considered the multi-user implications in your logic? Two people inserting at the same time (about the same time). What happens then??? Neither will see eachothers work, neither will block -- both will think "ah hah, I am first"... anyway, you can do too much work in triggers, this may well be that time -- there is nothing wrong with doing things in a more straightforward fashion trigger in oracle (eg: using a stored procedure to implement your transaction) but if you persist, you can use the technique: http://asktom.oracle.com/~tkyte/Mutate/index.html to avoid the mutating table constraint -- but I would avoid the situation that gets me there in the first place. The logic is a whole lot more understandable that way (and maintainable and testable and everything) Reviews Write a Review Ora-4091 May 05, 2003 - 5:45 pm UTC Reviewer: A reader We create trigger in the test server (8i) its working without error, and when we created at life (8) we get the following error: ORA-04091: table XXXX is mutating, trigger/function may not see it. Followup May 05, 2003 - 8:31 pm UTC they relaxed some of the constraining rules between 8.0 and 8.1 -- things are in general upwards (develop in 8.0 and goto 8.1) compatible but not backwards. Why I can't get the 4091 error when insert? January 05, 2004 - 3:39 am UTC Reviewer: Li ys from CHINA I only want to prove the mutating table by this triggers: CREATE TABLE r_Module ( Bureauno NUMBER(3), Moduleno NUMBER(3), primary key ( Bureauno, Moduleno ) ); CREATE OR REPLACE TRIGGER LimitTest BEFORE INSERT OR UPDATE ON r_Module FOR EACH ROW DECLARE v_MaxModuleNum CONSTANT NUMBER := 5; v_
RESOURCES Database Tools SQL Scripts & Samples Links » Database Forum » Slideshows » Sitemap Free Newsletters: DatabaseDaily News Via RSS Feed Database Journal |DBA Support |SQLCourse |SQLCourse2 Featured Database Articles Oracle Posted May 31, 2016 Mutating Table Error In Oracle: Why It Happens And What You Can Do About It By David Fitzjarrell The mutating table error in Oracle can be a familiar sight throughout a DBA's career, along with the usual question of 'Why?'. The error itself is not difficult to understand nor is it hazardous to the table data. Let's look at why the error is thrown, why it's not going to create data problems and how to possibly fix it. The first thing to realize is the table isn't actually mutating; the error is thrown because of the read consistency mechanism Oracle employs to ensure against 'dirty reads', which are reads of uncommitted changes. For all but the session performing the data modifications Oracle will use redo blocks to reconstruct the data image as of the starting time of the query. This prevents any uncommitted changes from being read. For the session performing the modifications the uncommitted changes are visible, but only after the modifications have completed. Insert 10 rows into a table, then query the count and you'll see there are 10 more rows than when you started. Every other session sees only the committed results. The mutating issue surfaces when a regular trigger attempts to modify the same table that was modified by the driving insert, update or delete statement. Since Oracle allows the modifying session to see its own changes Oracle tries to execute the trigger but fails when another DML statement tries to change incompletely modified data. Since the insert/update/delete can't complete until the trigger successfully executes, and executing the trigger would pile changes on top of changes in the middle of a transaction, Oracle doesn't allow it and throws the error. In a promoted v