Error ora 04091 - Исправление ошибок и поиск оптимальных решений проблем

Время прочтения
3 мин

Просмотры 35K

Рис.1 – художники мутанты ниндзя черепашки

Мутирование таблиц (ошибка ORA-04091) возникает, если в триггере уровня строки выполняется изменение или чтение данных из той же самой таблицы, для которой данный триггер должен был сработать.

Рассмотрим два способа обхода данной ситуации. Первый – через пакет – древний, как удар маваши гери с разворота, смотрится эффектно, но долго готовится и сложен в исполнении. Второй – более свежий и простой – с использованием составных триггеров.

create table turtles 
as
select 'Сплинтер' name, 'Крыса' essence from dual union all
select 'Леонардо', 'Художник' from dual union all
select 'Рафаэль', 'Художник' from dual union all
select 'Микеланджело', 'Художник'  from dual union all
select 'Донателло', 'Художник'  from dual;

NAME	ESSENCE
Сплинтер	Крыса
Леонардо	Художник
Рафаэль	Художник
Микеланджело	Художник
Донателло	Художник

Условимся, что когда Сплитер из крысы мутируют в сэнсэя, художники должны будут автоматически превратиться в ниндзя. Казалось бы, для этого должен подойти такой триггер

create or replace trigger tr_turtles_bue
before update of essence
on turtles
for each row
when (
  new.name = 'Сплинтер' and old.essence = 'Крыса' and new.essence = 'Сэнсэй'
)
begin
  update turtles
     set essence = 'Ниндзя'
   where essence = 'Художник';  
end;

Но при попытке обновить запись

update turtles
   set essence = 'Сэнсэй'
 where name = 'Сплинтер'

возникает
ORA-04091: table SCOTT.TURTLES is mutating, trigger/function may not see it

Удалим этот триггер

drop trigger tr_turtles_bue;

Способ обхода 1. С помощью пакета и триггера уровня инструкции.

create or replace package pkg_around_mutation 
is
  bUpdPainters boolean;
  procedure update_painters;  
end pkg_around_mutation;
/

create or replace package body pkg_around_mutation
is
  procedure update_painters
  is
  begin   
    if bUpdPainters then
      bUpdPainters := false;
      update turtles
         set essence = 'Ниндзя'
       where essence = 'Художник';
    end if;
  end;  
end pkg_around_mutation;
/

create or replace trigger tr_turtles_bue
before update of essence
on turtles
for each row
when (
  new.name = 'Сплинтер' and old.essence = 'Крыса' and new.essence = 'Сэнсэй' 
)
begin
  pkg_around_mutation.bUpdPainters := true;  
end tr_turtles_bue; 
/

create or replace trigger tr_turtles_bu
after update
on turtles
begin
  pkg_around_mutation.update_painters;  
end tr_turtles_bu;
/

Способ обхода 2. С помощью составного триггера (compound DML triggers).
Доступно, начиная с Oracle 11g.

create or replace trigger tr_turtles_ue
  for update of essence
  on turtles
  compound trigger
    bUpdPainters  boolean;
 
  before each row is
  begin
    if :new.name = 'Сплинтер' and :old.essence = 'Крыса' and :new.essence = 'Сэнсэй' then
      bUpdPainters := true;
    end if;
  end before each row;
  
  after statement is
  begin
    if bUpdPainters then
      update Turtles
         set essence = 'Ниндзя'
       where essence = 'Художник';
    end if;
  end after statement;
end tr_turtles_ue;

Пробуем

update turtles
   set essence = 'Сэнсэй'
 where name = 'Сплинтер'

NAME	ESSENCE
Сплинтер	Сэнсэй
Леонардо	Ниндзя
Рафаэль	Ниндзя
Микеланджело	Ниндзя
Донателло	Ниндзя

Даже если вы столкнулись с более сложным случаем мутации, можно использовать приведенную идею обхода. Она, во-первых, связана с тем, что в триггере уровня инструкции, в отличие от триггера уровня строки, мутации не возникает. Во-вторых, можно использовать либо переменные (признаки, защелки, таблицы PL SQL) в специально созданном вспомогательном пакете, либо переменные, глобальные для всех секций составного триггера, что более предпочтительно, начиная с версии Oracle 11g.

Так что теперь и вы знаете кунг-фу. До новых встреч.

Дополнительные материалы: Compound DML Triggers, Мутирование таблиц

Источник

Mutating table Error Occurs when Trigger is Querying or Modifying a “Mutating Table”

ORA-04091 is a very common error that occurs with triggers if triggers are not managed properly. A full understanding of triggers will help you avoid that error. A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-04091 error if you have a row trigger that reads or modifies the mutating table. For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error.

Another way that this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering from. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.

Let’s take an example in which we have to know total number Employees with Active status after any status is updated to ‘Active’. We will see it with an example. First let us create a table and then trigger.

Create a table and insert records into that table

Here is the table definition I have created and inserted some records.

SQL> DESC TEST;  
 Name            Null?    Type  
 -------------- -------- ----------------------------  
 ID                      NUMBER  
 NAME                    VARCHAR2(10)  
 SALARY                  NUMBER(20)  
 STATUS                  VARCHAR2(10)

SQL> SELECT * FROM TEST;  
    ID  NAME      SALARY STATUS  
---------- ---------- ---------- ----------  
    102 Ankit     8000   Active  
    104 Nikhil    69000  Active  
    105 Rajan     18000  InActive  
    107 Karan     101000 Active  
    110 Sajal     88000  InActive  
    101 Ravi      89000  Active  
    109 Manu      777000 InActive  
7 rows selected.

Now, Let’s create a Mutating Trigger (Row Level) on Update of Status column of above table.

SQL> CREATE OR REPLACE TRIGGER MutatingTrigger  /*Row Level TRIGGER*/
 2 AFTER UPDATE OF STATUS ON TEST  
 3 FOR EACH ROW  
 4 DECLARE  
 5 V_count NUMBER;  
 6 BEGIN  
 7 Select count(*) Into V_count From TEST  
 8 Where STATUS='Active';  
 9 DBMS_OUTPUT.PUT_LINE('Total Number of Active Records: '|| V_count);  
 10 END;  
 11 /  
Trigger created.

If we try to change status of any record to “Active”, Oracle will throw a Mutating Table Error as we are trying to update the records and trigger is trying to select affected records in the same trigger.

SQL> UPDATE TEST  
 2 SET STATUS='Active' Where ID IN(109,110);  
UPDATE TEST  
    *  
ERROR at line 1:  
ORA-04091: table HR.TEST is mutating, trigger/function may not see it  
ORA-06512: at "HR.MUTATINGTRIGGER", line 4  
ORA-04088: error during execution of trigger 'HR.MUTATINGTRIGGER

So, When you encounter an ORA-04091 error, the following error message will appear:

ORA-04091: table name is mutating, trigger/function may not see it
Cause - A statement executed a Trigger or custom PL/SQL Functions. 
That trigger/function tried to modify or query a table that is currently being modified 
by the statement that fired the trigger/function.

Solution 1

Create Statement Level Trigger instead of Row Level Trigger. Row Level Triggers can’t read Mutating tables- Change Row Level Trigger to Statement Level Trigger. IF we omit the ‘For Each Row’ clause from above Trigger, it will become statement level Trigger. Let’s modify above trigger to statement level trigger.

SQL> CREATE OR REPLACE TRIGGER MutatingTrigger  /*Statement Level TRIGGER*/
 2 AFTER UPDATE OF STATUS ON TEST  
 3 DECLARE  
 4 V_count NUMBER;  
 5 BEGIN  
 6 Select count(*) Into V_count From TEST  
 7 Where STATUS='Active';  
 8 DBMS_OUTPUT.PUT_LINE('Total Number of Active Records: '|| V_count);  
 9 END;  
 10 /  
Trigger created.

Let’s try to fire the same Update statement now and see if you still get Mutating Error.

SQL> UPDATE TEST  
 2 SET STATUS='Active' Where ID IN(109,110);  
Total Number of Active Records: 6  
2 rows updated.

SQL> SELECT * From TEST;  
    ID NAME      SALARY STATUS  
---------- ---------- ---------- ----------  
    102 Ankit    8000   Active  
    104 Nikhil   69000  Active  
    105 Rajan    18000  InActive  
    107 Karan    101000 Active  
    110 Sajal    88000  Active  
    101 Ravi     89000  Active  
    109 Manu     777000 Active  
7 rows selected.

As you can see it works fine with Statement Level Trigger. With ‘For Each Row’, session cannot query the same table. This restriction applies to all row level triggers and hence we run into mutating table error.

Solution 2

Make the transaction independent using PRAGMA AUTONOMOUS TRANSACTION. Declare a Row level trigger as an Autonomous Transaction so that it is not in the same scope of the session issuing DML statements.

The AUTONOMOUS_TRANSACTION PRAGMA changes the way a subprogram works within a transaction. A subprogram marked with this PRAGMA can do SQL operations and commit or rollback those operations, without committing or rolling back the data in the main transaction. Autonomous transactions allow you to leave the context of the calling transaction, perform an independent transaction, and return to the calling transaction without affecting its state.

Following is the ROW Level Trigger defined as PRAGMA AUTONOMOUS TRANSACTION.

SQL> CREATE OR REPLACE TRIGGER MutatingTrigger  
 2 AFTER UPDATE OF STATUS ON TEST  
 3 FOR EACH ROW  
 4 DECLARE  
 5 V_count NUMBER;  
 6 PRAGMA AUTONOMOUS_TRANSACTION;  
 7 BEGIN  
 8 Select count(*) Into V_count From TEST  
 9 Where STATUS='Active';  
 10 DBMS_OUTPUT.PUT_LINE('Total Number of Active Records: '|| V_count);  
 11 END;  
 12 /  
Trigger created.

Now let’s issue the Update statement again and see if now it works with ROW Level Trigger as now we have Created a Trigger with PRAGMA.

SQL> Select * From TEST;  
    ID NAME      SALARY STATUS  
---------- ---------- ---------- ----------  
    102 Ankit      8000 Active  
    104 Nikhil     69000 Active  
    105 Rajan      18000 InActive  
    107 Karan     101000 Active  
    110 Sajal      88000 InActive  
    101 Ravi      89000 Active  
    109 Manu      777000 InActive  
7 rows selected.

SQL> UPDATE TEST  
 2 SET STATUS='Active' Where ID IN(109,110);  
Total Number of Active Records: 4  
Total Number of Active Records: 4  
2 rows updated.

SQL> SELECT * From TEST;  
    ID NAME      SALARY STATUS  
---------- ---------- ---------- ----------  
    102 Ankit      8000 Active  
    104 Nikhil     69000 Active  
    105 Rajan      18000 InActive  
    107 Karan     101000 Active  
    110 Sajal      88000 Active  
    101 Ravi      89000 Active  
    109 Manu      777000 Active  
7 rows selected.

By defining Row Level trigger as an AUTONOMOUS TRANSACTION, we got rid of Mutating table error but result is not correct. Updated records are not getting reflected. So one has to be very careful while using this approach.

Solution 3

Avoid Mutating Error Using Compound Trigger. In Oracle 11g, Oracle has made is much easier by introducing Compound Trigger. A compound trigger allows code for one or more timing points for a specific object to be combined into a single trigger. The individual timing points can share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up.

Let’s create a Compound Trigger to resolve Mutating Table Error.

SQL> CREATE OR REPLACE TRIGGER MutatingTrigger  
 2 FOR UPDATE  
 3 ON TEST  
 4 COMPOUND TRIGGER  
 5 /*Declaration Section*/  
 6 V_count NUMBER;  
 7  
 8 AFTER EACH ROW IS  
 9 BEGIN  
 10 DBMS_OUTPUT.PUT_LINE('Record Updated');  
 11 END AFTER EACH ROW;  
 12  
 13 AFTER STATEMENT IS  
 14 BEGIN  
 15 Select count(*) Into V_count  
 16 From TEST Where STATUS = 'Active';  
 17 DBMS_OUTPUT.PUT_LINE('Total Number of Active Records: '|| V_count);  
 18 END AFTER STATEMENT;  
 19  
 20 END MutatingTrigger;  
 21 /  
Trigger created.

Now let’s issue the Update statement and see how it works with Compound Trigger.

SQL> SELECT * From TEST;  
    ID NAME      SALARY STATUS  
---------- ---------- ---------- ----------  
    102 Ankit      8000 Active  
    104 Nikhil     69000 Active  
    105 Rajan      18000 InActive  
    107 Karan     101000 Active  
    110 Sajal      88000 InActive  
    101 Ravi      89000 Active  
    109 Manu      777000 InActive  
7 rows selected.

SQL> UPDATE TEST  
 2 SET STATUS='Active' Where ID IN(109,110);  
Record Updated  
Record Updated  
Total Number of Active Records: 6  
2 rows updated.

SQL> SELECT * From TEST;  
    ID NAME      SALARY STATUS  
---------- ---------- ---------- ----------  
    102 Ankit      8000 Active  
    104 Nikhil     69000 Active  
    105 Rajan      18000 InActive  
    107 Karan     101000 Active  
    110 Sajal      88000 Active  
    101 Ravi      89000 Active  
    109 Manu      777000 Active  
7 rows selected.

Here we get correct result without getting any Mutating Table error.

Example Workaround for ORA-4091 Error

The following is the sample work-around that used the EMP table.

1. Create the package to define the objects that will house the data.

create or replace package emp_pkg as 
  type emp_tab_type is table of rowid index by binary_integer;
  emp_tab emp_tab_type;
  emp_index binary_integer;
end emp_pkg;
/

2. Create the table level trigger to reset the index BEFORE any changes take place.

create or replace trigger emp_bef_stm_all before insert or update or delete on emp
begin 
  --Remember to reset the pl/sql table before each statement  
  emp_pkg.emp_index := 0;
end;
/

3. The following Row level trigger will fire for each row being modified and copy the RowID for each row to the EMP_TAB object defined within the package.

create or replace trigger emp_aft_row_all after insert or update or delete on emp
for each row
begin 
   --Store the rowid of updated record into global pl/sql table
   emp_pkg.emp_index := emp_pkg.emp_index + 1;
   emp_pkg.emp_tab(emp_pkg.emp_index) := :new.rowid;
end;
/

4. The following table level trigger will fire AFTER the others and will print out each RowID affected.

create or replace trigger emp_aft_stm_all after insert or update or delete on emp
begin
   for i in 1 .. emp_pkg.emp_index loop 
      -- Re-process the updated records.
      --There is no restriction here.
      dbms_output.put_line(emp_pkg.emp_tab(i));
   end loop;
emp_pkg.emp_index := 0;
end;
/

5. In order to see how this work from SQL*Plus, the output must be turned on.

This first example, displays the RowIDs for Department 20 then issues a DELETE command. The trigger should fire and display the same RowIDs to confirm it is working. (The RowIDs will vary from machine to machine.)

SQL> SELECT ROWID, DEPTNO FROM EMP WHERE DEPTNO = 20;

ROWID DEPTNO
------------------ ----------
AAAM4iAAEAAAAG+AAA 20
AAAM4iAAEAAAAG+AAD 20
AAAM4iAAEAAAAG+AAH 20
AAAM4iAAEAAAAG+AAK 20
AAAM4iAAEAAAAG+AAM 20

SQL> DELETE EMP WHERE DEPTNO = 20;
AAAM4iAAEAAAAG+AAA
AAAM4iAAEAAAAG+AAD
AAAM4iAAEAAAAG+AAH
AAAM4iAAEAAAAG+AAK
AAAM4iAAEAAAAG+AAM

This second example, displays the RowIDs for Department 10 then issues an UPDATE command. The trigger should fire and display the same RowIDs to confirm it is working. (The RowIDs will vary from machine to machine.)

SQL> SELECT ROWID, DEPTNO FROM EMP WHERE DEPTNO=10;

ROWID DEPTNO
------------------ ----------
AAAM4iAAEAAAAG+AAG 10
AAAM4iAAEAAAAG+AAI 10
AAAM4iAAEAAAAG+AAN 10

SQL> UPDATE EMP SET JOB='Test' WHERE DEPTNO=10;
AAAM4iAAEAAAAG+AAG
AAAM4iAAEAAAAG+AAI
AAAM4iAAEAAAAG+AAN

3 rows updated.

6. To rollback the changes, issue the Rollback command.

SQL> ROLLBACK;
Rollback complete.

Источник

I am using oracle 11g R2 — 11.2.0.1
I have following function to fetch the ID from name supplied
create or replace
FUNCTION get_group_id
(p_groupname IN group_list.groupname%TYPE)
RETURN group_list.group_id%TYPE
AS
v_group_id group_list.group_id%TYPE;
BEGIN
SELECT group_list.group_id
INTO v_group_id
FROM group_list
WHERE group_list.groupname = p_groupname;
RETURN v_group_id;
END get_group_id;
/

and I am doing simple update command to the table, but its giving me ORA-04091 error. Any help is appreciated

update GROUP_LIST set MGR_GROUP_ID = get_group_id(‘manager1′) where group_id = get_group_id(’employee51′);

Error which I am getting is —
Error report:
SQL Error: ORA-04091: table is mutating, trigger/function may not see it
ORA-06512: at «GET_GROUP_ID», line 7
04091. 00000 — «table %s.%s is mutating, trigger/function may not see it»
*Cause: A trigger (or a user defined plsql 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.

Источник

Summary: in this tutorial, you will learn about the mutating table error in Oracle and how to fix it using a compound trigger.

When a table is mutating, it is changing. If the changing is taking place and you try to make another change in the middle of the first change, Oracle will issue a mutating table error with the error code ORA-04091.

Specifically, the error results from the following operations:

First, you update data to a table.
Second, a row-level trigger associated with the table automatically fires and makes another change to the table.

Simulate the mutating table error example

Let’s use the customers table from the sample database for demonstration.

Suppose you want to update the credit limit for a customer. If the credit is greater than 5 times of the lowest non-zero credit, the system automatically assigns this credit to the customer.

CREATE OR REPLACE TRIGGER customers_credit_policy_trg 
    AFTER INSERT OR UPDATE 
    ON customers
    FOR EACH ROW 
DECLARE 
    l_max_credit   customers.credit_limit%TYPE; 
BEGIN 
    -- get the lowest non-zero credit 
    SELECT MIN (credit_limit) * 5 
        INTO l_max_credit 
        FROM customers
        WHERE credit_limit > 0;
    
    -- check with the new credit
    IF l_max_credit < :NEW.credit_limit 
    THEN 
        UPDATE customers 
        SET credit_limit = l_max_credit 
        WHERE customer_id = :NEW.customer_id; 
    END IF; 
END;
/
Code language: SQL (Structured Query Language) (sql)

This statement updates the credit limit of the customer 1 to 12000:

UPDATE customers
SET credit_limit = 12000
WHERE customer_id = 1;
Code language: SQL (Structured Query Language) (sql)

The update action fires the trigger and Oracle issues the following mutating table error:

ORA-04091: table OT.CUSTOMERS is mutating, trigger/function may not see it
Code language: SQL (Structured Query Language) (sql)

As explained earlier, the update statement changes the data of the customers table. The trigger fires and attempts to make another change while the first change is in the progress, which results in an error.

To fix the mutating table error, you can use a compound trigger if you are using Oracle 11g and later.

Note that if you’re using Oracle 10g or earlier, you need to use a package to fix the mutating table error, which we will not cover in this tutorial.

CREATE OR REPLACE TRIGGER customers_credit_policy_trg    
    FOR UPDATE OR INSERT ON customers    
    COMPOUND TRIGGER     
    TYPE r_customers_type IS RECORD (    
        customer_id   customers.customer_id%TYPE, 
        credit_limit  customers.credit_limit%TYPE    
    );    

    TYPE t_customers_type IS TABLE OF r_customers_type  
        INDEX BY PLS_INTEGER;    

    t_customer   t_customers_type;    

    AFTER EACH ROW IS    
    BEGIN  
        t_customer (t_customer.COUNT + 1).customer_id :=    
            :NEW.customer_id;    
        t_customer (t_customer.COUNT).credit_limit := :NEW.credit_limit;
    END AFTER EACH ROW;    

    AFTER STATEMENT IS    
        l_max_credit   customers.credit_limit%TYPE;    
    BEGIN      
        SELECT MIN (credit_limit) * 5    
            INTO l_max_credit    
            FROM customers
            WHERE credit_limit > 0;

        FOR indx IN 1 .. t_customer.COUNT    
        LOOP                                      
            IF l_max_credit < t_customer (indx).credit_limit    
            THEN    
                UPDATE customers    
                SET credit_limit = l_max_credit    
                WHERE customer_id = t_customer (indx).customer_id;    
            END IF;    
        END LOOP;    
    END AFTER STATEMENT;    
END; 
Code language: SQL (Structured Query Language) (sql)

In this trigger:

First, declare an array of customer record that includes customer id and credit limit.
Second, collect affected rows into the array in the row-level trigger.
Third, update each affected row in the statement-level trigger.

In this tutorial, you have learned about the mutating table error in Oracle and how to fix it using a compound trigger.

Источник

Summary:

In this post, I will explain what’s this ORA-04091 error is and why you are frequently getting it. After that I demonstrate how to fix Oracle Mutating Trigger Table Error (Ora-04091).

The table is the most important part of the Oracle database. This database works as RDBMS (Relational Database Management System) and to relate the stored information with each other table is used. In a table, all entered information is stored and relate to each other. And if the table of any database gets corrupted then all the stored data gets inaccessible or unreachable.

Oracle database has a different trigger concept as MS SQL Server; it has a set-based approach. All the rows that are exaggerated by the data alteration like the update, insert, delete are stored in the deleted and stored tables. A regular trigger DML is used in MS SQL, it regularly executes after the statement. Before the image is stored in the deleted table and after the image stored in the inserted table and both are accessed within the trigger. You can also join the deleted and inserted table and used them to update the table.

Recursive trigger error and mutating table error are related in the logic that they are invoked when the table on which the trigger has been called up is customized or accessed by a similar trigger but should not be perplexed with each other. When users doing modification on a similar table in a per statement trigger so that trigger fire recursively again and recursion error occurs.

To understand Oracle Mutating Trigger Table Error more clearly suppose a statement executed the custom PL/SQL function or trigger. That statement’s trigger or function is trying to change the table that is already being modified by the statement which fired the function/trigger.

Error Detail:

Error code: ORA-04091

Error description: ORA-04091: table name is mutating, trigger/function may not see it

Screenshot:

What Causes Oracle Mutating Trigger Table Error?

Oracle mutating trigger table error (ORA-04091) mainly comes when the row-level trigger attempting to modify or examine those tables which are already undertaking changes through the DELETE, INSERT, or UPDATE statement.

Particularly, this error arises when the row-level trigger tries to write or read those tables from which the trigger was executed.

How To Fix Oracle Mutating Trigger Table Error (Ora-04091)?

Method 1# Rewrite The Trigger As Statement-Level Trigger

Oracle Mutating Trigger Table Error only put an impact on the row-level triggers. So to fix this issue the very option that you must try is rewriting the trigger as the statement-level trigger.

For using the statement level trigger, the very first thing you need to do is to preserve some data from each row. Later on, it will be used by the statement level trigger.

You can store this data either in a temporary table or in the PL/SQL collection. For achieving the required output, a row-level trigger that causes the mutating trigger table error may result in a complicated statement-level trigger.

Follow these triggers criteria before using it:

Triggers don’t commit transactions. So, if any transaction is rolled back, then entire data modified by the trigger also been rolled back.
In the trigger body, you can’t use the save points, Commits, and rollbacks. The entire transaction is get affected by this commit/rollback.
Unhandled exceptions within the trigger result in the rollbacking of the entire transaction. So don’t consider it only as a trigger.
On the delete triggers have no: NEW values.
When several triggers are defined in the event, their order of being fired is not defined. So if you want that the trigger must be fired in order then you have to create one separate trigger which will execute all the action in order.
On the insert triggers have no: OLD values.
One trigger can easily affect other events for executing their triggers.

Method 2# Avoid Using Triggers

Another way to avoid oracle mutating trigger table error is not to use the triggers.

Currently, the object-oriented Oracle offers “methods” which are mainly associated with database tables. So, even the several PL/SQL developers try to avoid usage of triggers until and unless it is very much necessary.

Method 3# Use The “after” Or “instead of” Trigger

To sort out the mutating trigger table error another possible solution that you can try is using the “after” or “instead of” trigger. As, this will help you to avoid the currency issue regarding the mutating table.

Suppose, using the trigger “:after update on xxx”, the table will not get mutated and the original update will get over.

Method 4# Use Autonomous Transactions And Trigger Syntax

To avoid this Mutating Trigger Table Error you can use the combination of statement-level triggers and row-level.

Apart from this, you can also mark your trigger as an autonomous transaction which will make it independent from the database table which calls up the procedure.

Method 5# Go With The Recommended Option

To fix any big corruption or to repair any large database you can use any third-party repair tool like Oracle File Repair tool. This tool very effectively repairs corrupted or damaged Oracle databases without any data loss. If your database or table of the database gets corrupted and displays any error message then you can apply this tool to repair and recover the corrupted data.

This tool provides users with a very simple user interface so that it doesn’t require typing any commands on the command line. So download the Oracle database repair tool and apply it to recover your corrupted database table.

It is a professional tool and it is capable of fixing any errors related to the Oracle. It is a powerful and comprehensive software and it can be used to recover Oracle database file that is corrupted or damaged.

Steps To Repair Corrupt Or Damaged Oracle Databases

Step 1: Search the Initial screen of Oracle File Repair tool with a pop-up window showing options to select or search corrupt Oracle databases on your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.

Wrap Up:

I have shared all the possible fixes to resolve Oracle error 04091. But if you are getting any kind issue meanwhile performing these fixes then let me know by asking your queries on our FB and Twitter page.

So share your problems which you are frequently getting meanwhile working with the Oracle database. I will try my best to provide the best possible solution to fix it…!

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.

Источник

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze
and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger
tries to examine or change a table that is already undergoing change (via an INSERT,
UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the
table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the «traditional» solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1, to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.

How to Get a Mutating Table Error

I need to implement this rule on my employees table:

Your new salary cannot be more than 25x the lowest salary in the company. Your salary will be automatically set to the maximum allowed, if this rule is broken.

So just check to see if that rule is violated, right? Easy enough in PL/SQL, right in a database trigger (see links at bottom of post for discussions on whether or not you should put logic like this in your database triggers):

CREATE OR REPLACE TRIGGER equitable_salary_trg 
   AFTER INSERT OR UPDATE 
   ON employees 
   FOR EACH ROW 
DECLARE 
   l_max_allowed   employees.salary%TYPE; 
BEGIN 
   SELECT MIN (salary) * 25 
     INTO l_max_allowed 
     FROM employees; 
 
   IF l_max_allowed < :NEW.salary 
   THEN 
      UPDATE employees 
         SET salary = l_max_allowed 
       WHERE employee_id = :NEW.employee_id; 
   END IF; 
END equitable_salary_trg;

Well….maybe not. I execute the following block:

BEGIN 
   UPDATE employees 
      SET salary = 100000 
    WHERE last_name = 'King'; 
END;

and I see this error:

ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "EQUITABLE_SALARY_TRG", line 4

OK, we get that, right? I am both selecting from and trying to update the EMPLOYEES table in a row-level trigger. That’s the no-no.

Getting Around ORA-04091 with PL/SQL Packages

The solution, conceptually, is simple enough. If I can do task X in the row level trigger, save whatever information I need to perform X on that row in a to-do list (a collection, perhaps?). Then define an AFTER STATEMENT trigger that goes through the to-do list, and executes the desired logic for each row.

The traditional (now, out-of-date) solution is to define a package that contains a collection defined at the package level. Package-level variables have session scope. So I can add information to the collection within the row-level trigger, and it will still be there when I bubble up to the statement-level trigger.

Here’s my package specification:

CREATE OR REPLACE PACKAGE equitable_salaries_pkg 
IS 
   PROCEDURE initialize; 
 
   PROCEDURE add_employee_info ( 
      employee_id_in IN employees.employee_id%TYPE 
    , salary_in IN employees.salary%TYPE 
   ); 
 
   PROCEDURE make_equitable; 
END equitable_salaries_pkg;

Huh. I don’t see any collection there. Right. You shouldn’t. If you put the collection in the specification, it can be modified by any schema with EXECUTE authority on the package, in whatever way anyone wants to mess with that collection. Well, that’s no good. So I «hide» the list in the body and «expose» it through the procedures in the spec.

CREATE OR REPLACE PACKAGE BODY equitable_salaries_pkg   
IS   
   TYPE id_salary_rt IS RECORD (   
      employee_id   employees.employee_id%TYPE   
    , salary        employees.salary%TYPE   
   );   
   
   TYPE g_emp_info_t IS TABLE OF id_salary_rt   
      INDEX BY PLS_INTEGER;   
   
   g_emp_info                 g_emp_info_t;   
   g_corrections_in_process   BOOLEAN      := FALSE;   
   
   PROCEDURE initialize   
   IS   
   BEGIN   
      g_emp_info.DELETE;   
   END initialize;   
   
   PROCEDURE finished_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := FALSE;   
   END finished_corrections;   
   
   PROCEDURE starting_corrections   
   IS   
   BEGIN   
      g_corrections_in_process := TRUE;   
   END starting_corrections;   
   
   FUNCTION corrections_in_process   
      RETURN BOOLEAN   
   IS   
   BEGIN   
      RETURN g_corrections_in_process;   
   END corrections_in_process;   
   
   PROCEDURE add_employee_info (   
      employee_id_in IN employees.employee_id%TYPE   
    , salary_in IN employees.salary%TYPE   
   )   
   IS   
      l_index   PLS_INTEGER := g_emp_info.COUNT + 1;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         g_emp_info (l_index).employee_id := employee_id_in;   
         g_emp_info (l_index).salary := salary_in;  
      END IF;   
   END add_employee_info;   
   
   PROCEDURE make_equitable   
   IS   
      l_max_allowed   employees.salary%TYPE;   
      l_index         PLS_INTEGER;   
   BEGIN   
      IF NOT corrections_in_process   
      THEN   
         starting_corrections;   
   
         SELECT MIN (salary) * 25   
           INTO l_max_allowed   
           FROM employees;      
   
         WHILE (g_emp_info.COUNT > 0)   
         LOOP   
            l_index := g_emp_info.FIRST; 
   
            IF l_max_allowed < g_emp_info (l_index).salary   
            THEN   
               UPDATE employees   
                  SET salary = l_max_allowed   
                WHERE employee_id = g_emp_info (l_index).employee_id;   
            END IF;   
   
            g_emp_info.DELETE (g_emp_info.FIRST);   
         END LOOP;   
   
         finished_corrections;   
      END IF;   
   END make_equitable;   
END equitable_salaries_pkg;

See? Aren’t you glad I wrote that, so you didn’t have to? Well, it gets better — as in lots of that code is unnecessary. But before I get to that, let’s finish up the old-style approach. We need to rebuild the triggers!

1. Before getting started, make sure no one is going to muck with those rows. And make sure the package-based collection is empty.

CREATE OR REPLACE TRIGGER equitable_salaries_bstrg 
   before INSERT OR UPDATE  
   ON employees 
BEGIN 
   LOCK TABLE employees IN EXCLUSIVE MODE; 
   equitable_salaries_pkg.initialize; 
END;

2. For each insert or update to employees, add the necessary information to the to-do list.

CREATE OR REPLACE TRIGGER equitable_salaries_rtrg  
   AFTER INSERT OR UPDATE OF salary  
   ON employees  
   FOR EACH ROW  
BEGIN  
   equitable_salaries_pkg.add_employee_info (
      :NEW.employee_id, :NEW.salary);  
END;

3. Create a statement-level trigger to apply the rule.

CREATE OR REPLACE TRIGGER equitable_salaries_astrg  
   AFTER INSERT OR UPDATE   
   ON employees  
BEGIN  
   equitable_salaries_pkg.make_equitable;  
END;

And now the update statement will work without raising any ORA-04091 errors!

BEGIN  
   UPDATE employees  
      SET salary = 100000  
    WHERE last_name = 'King';  
  
   ROLLBACK;  
END; 

add_employee_info: 100-100000
add_employee_info: 156-100000
make_equitable max allowed 52500
make_equitable emp id and salary: 100-100000

Yep. That’s a lot of code to write and deal with to get around this problem. So several years back, the PL/SQL team decided to make things easier for their users with….

Straight from the doc: A compound DML trigger created on a table or editioning view can fire at multiple timing points. Each timing point section has its own executable part and optional exception-handling part, but all of these parts can access a common PL/SQL state. The common state is established when the triggering statement starts and is destroyed when the triggering statement completes, even when the triggering statement causes an error. Two common uses of compound triggers are: (1) To accumulate rows destined for a second table so that you can periodically bulk-insert them; (2) To avoid the mutating-table error (ORA-04091).

The compound trigger more allows you to define variables which persist through the execution of the steps defined in the compound trigger. And that’s the aspect of this feature that makes things so much easier when it comes to mutable table errors.

Using this feature, I can combine all the different trigger events and code, plus they share scope like the subprograms of a package body. So I declare a variable in the compound trigger and reference it in both trigger events. Take a look:

CREATE OR REPLACE TRIGGER equitable_salary_trg    
FOR UPDATE OR INSERT ON employees    
COMPOUND TRIGGER     
   TYPE id_salary_rt IS RECORD (    
      employee_id   employees.employee_id%TYPE    
    , salary        employees.salary%TYPE    
   );    
    
   TYPE row_level_info_t IS TABLE OF id_salary_rt  INDEX BY PLS_INTEGER;    
    
   g_row_level_info   row_level_info_t;    
    
   AFTER EACH ROW IS    
   BEGIN  
      g_row_level_info (g_row_level_info.COUNT + 1).employee_id :=    
           :NEW.employee_id;    
      g_row_level_info (g_row_level_info.COUNT).salary := :NEW.salary;
   END AFTER EACH ROW;    
    
   AFTER STATEMENT IS    
      l_max_allowed   employees.salary%TYPE;    
   BEGIN      
      SELECT MIN (salary) * 25    
        INTO l_max_allowed    
        FROM employees;     
       
      FOR indx IN 1 .. g_row_level_info.COUNT    
      LOOP                                      
         IF l_max_allowed < g_row_level_info (indx).salary    
         THEN    
            UPDATE employees    
               SET salary = l_max_allowed    
             WHERE employee_id = g_row_level_info (indx).employee_id;    
         END IF;    
      END LOOP;    
   END AFTER STATEMENT;    
END equitable_salary_trg;

Much simpler — all relevant code in one place.

More reliable — you don’t have to worry about managing the session-persistent collection.

Less code — always a nice thing, as long as the «less code» is also understandable and easy to maintain.

You might also find these resources helpful:

ORACLE-BASE: Trigger Enhancements in Oracle Database 11g Release 1
ORACLE-BASE: Should you use triggers at all? (Facts, Thoughts and Opinions)
Toon Koopelars: Triggers Considered Harmful, Considered Harmful

Источник

The Mutating table error is a well-known problem encountered
in development; most developers have come across this error.

ORA-04091: table <tablename> is mutating, 
	trigger/function may not see it

The basic reason for this error is the way Oracle
manages a read consistent view of data. The error is encountered when a
row-level trigger accesses the same table on which it is based, while
executing. The table is said to be mutating. Mutation will not occur if a
single record is inserted in the table (using VALUES clause). If bulk insertion
is done or data is inserted from another table mutation will occur.

The mutating error is not only encountered during queries,
but also for insert, updates and deletes present in the trigger. Below is a
table that explains the various transaction scenarios that involves a trigger
and whether it is prone to generate the mutating error. The OPERATION column
explains the DML activity being performed and the TYPE column lists the type of
trigger created and the execution level.

Case 1: When Trigger on table refers the same table:
—————————————————————–
OPERATION TYPE MUTATING?
—————————————————————–
insert before/statement-level No
insert after/statement-level No
update before/statement-level No
update after/statement-level No
delete before/statement-level No
delete after/statement-level No

insert before/row-level Single row Multi-row
No Yes
insert after/row-level Yes
update before/row-level Yes
update after/row-level Yes
delete before/row-level Yes
delete after/row-level Yes
—————————————————————–

A very simple example is given below.



SQL> create table am27

  2  (col1  number,

  3   col2  varchar2(30));
Table created.
SQL> create or replace trigger am27_trg

  2  before insert or update or delete

  3  on am27

  4  for each row

  5  declare

  6    l_chk pls_integer;

  7  begin

  8    select count(1)

  9    into   l_chk

 10    from   am27;

 11    — more processing…

 12  end;

 13  /
Trigger created.
SQL> insert into am27 values (1, ‘testing’);
1 row created.
SQL> update am27

  2  set    col1 = 2;

update am27

       *

ERROR at line 1:

ORA-04091: table SYSTEM.AM27 is mutating, trigger/function may not see it

ORA-06512: at “SYSTEM.AM27_TRG”, line 4

ORA-04088: error during execution of trigger ‘SYSTEM.AM27_TRG’

In the above example, as table AM27 is being queried in the
trigger AM27_TRG that is based on the same table, a mutating error is received.

It is also possible for ORA-4091 to be encountered when
querying a table other than the table on which the trigger is based! This
happens when a foreign key reference is present with an on-delete-cascade
option. A row level trigger on the master table will mutate if the detail table
is being referred to in the trigger, for a delete transaction. This will only
happen if the foreign key on the detail table is created with the on delete
cascade option. No mutation occurs if the master table is being referred in a
trigger on the detail table.

There is one odd case where mutation may occur when some
other table in the trigger is referred to; below is an example of such a condition.

AM10 is a master table. AM10_DTL is the detail table that is
related to the master table with the on-delete-cascade option. AM10_BEF_TRG is
created on the master table that queries the detail table for some information.
Issuing a delete on the master table results in the mutation error.



SQL> create table am10

  2  (col1 number, col2 varchar2(10));
Table created.
SQL> create table am10_dtl

  2  (col1  number,

  3   col2  varchar2(10));
Table created.
SQL> alter table am10 add primary key (col1);
Table altered.
SQL> alter table am10_dtl add foreign key (col1) references am10(col1) on delete cascade;
Table altered.
SQL> create or replace trigger am10_bef_trg

  2  before insert or update or delete on am10

  3  for each row

  4  declare

  5    l_chk pls_integer;

  6  begin

  7    select 1

  8    into   l_chk

  9    from   am10_dtl

 10    where  col1 = :new.col1;

 11    dbms_output.put_line(‘ok’);

 12  exception

 13    when no_data_found then

 14     dbms_output.put_line(‘no dtl recs’);

 15  end;

 16  /



  


                       
  




Trigger created.
SQL> insert into am10 values (1, ‘amar’);

err
1 row created.
SQL> insert into am10 values (2, ‘chk’);

err
1 row created.
SQL> insert into  am10_dtl values(1, ‘cooler’);
1 row created.
SQL> insert into am10_dtl values (2, ‘validator’);
1 row created.
SQL> delete from am10 where col1= 1;

delete from am10 where col1= 1

            *

ERROR at line 1:

ORA-04091: table SYSTEM.AM10_DTL is mutating, trigger/function may not see it

ORA-06512: at “SYSTEM.AM10_BEF_TRG”, line 4

ORA-04088: error during execution of trigger ‘SYSTEM.AM10_BEF_TRG’

Conclusion

Maintaining a consistent view of the data is an important
feature of Oracle. The mutating error conflicts with maintaining a consistent
view, therefore, care should be taken to write proper code and avoid such
triggers. It is for the developers to write proper logic so that such
complications do not arise.

If there is a requirement to update the base table from the row-level trigger,
then split the logic across multiple triggers. The required information can be
stored in a temporary table, PL/SQL table or package variables when the
row-level trigger is executed. A statement-level trigger can then be used to
pickup the stored information and apply it to the table.

See All Articles by Columnist Amar Kumar Padhi

Источник