ORA-04091错误解决
ORA-04091错误是常见的一个错误,下面这个描述了该错误及在那种情况下触发器会产生这个错误。
原文链接出处:http://www.databasejournal.com/features/oracle/article.php/3329121
The Mutating table error is a well-known problem encountered in development; most developers have come across this error.
ORA-04091: table
<tablename></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:
ORA04091: 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.
分享到:
相关推荐
ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法 ora-00604 错误 解决 方法ora-00604 错误 解决 方法
创建物化视图ORA-12014错误解决方法 创建物化视图ORA-12014错误解决方法
Drop goldengate用户时,报ORA-00604 ORA-20782 ORA-06512错误
oracle的ORA-12514错误 解决办法
用oracle数据库新建连接时遇到ora-12505,此问题解决后又出现ora-12519错误,郁闷的半天,经过一番折腾问题解决,下面小编把我的两种解决方案分享给大家,仅供参考。 解决方案一: 今天工作时在新建连接的时候遇到...
关于WIN10系统使用oracle instant client 时候提示ORA-01019错误的解决方案,本方案是配置好环境变量后依然提示ORA-01019错误的解决方案,内附本人制作测试的全过程说明
在oracle数据库遇到ora-227101错误的解决办法
ora-12519错误解决方案
在oracle里面运行一下,解决Exception java.sql.SQLException ORA-00600 内部错误代码
NULL 博文链接:https://ajita.iteye.com/blog/1725304
在运行查询SELECT * FROM V$SESSION 会出现ORA-29275:部分多字节字符的错误,这是什么原因开始我不得其解,网上也没有介绍什么好办法。本文给出答案。
离线误删空间文件导致的ORA-01033及ORA-01145问题的解决办法,在解决ORA-01033的过程中,又出现ORA-01145 * 第 1 行出现错误: ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机 接着的解决步骤
如果内存块仍然不够满足需求,那么就会出现ORA- 04031错误。这些错误同样可能发生在ASM的实例中。默认的共享池的大小基本能够满足大部分的环境,但是如果遇到ORA-04031错误的时候可能就需要增大。 当遇到这个错误的...
数据库常见错误解决方法,日志无法归档或者操作出项错误
ORA-01033ORACLE错误解决
ERwin连接oracle报ORA-01041内部错误,hostdef扩展名不存在解决办法,实验可解决问题。
ORA-00312: online log 1 thread 1: '/oradata/10g/db/instant/redo01.log' SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=4294967296 SCOPE=BOTH; System altered. SQL> shutdown immediate ORA-...
win 8 升级 win 10 后,oracle 11g 无法启动及客户端连接,命令行sqlplus登录,startup 时报 ORA-00119 和 ORA-00132 的错误。 1. sqlplus /nolog 2. conn / as sysdba 3. startup 报错: ORA-00119 和 ORA-00132
错误描述:oracle远程连接服务器出现 ORA-12170 TNS:连接超时 错误检查:有很多是oracle自身安装的问题,但是我这里服务器配置正常,监听正常,服务正常,远程可以ping通服务器。 这里主要是防火墙问题,解决办法: ...
ORA-06512 数字或值错误,字符缓冲区太小的解决方法。 有图有真相,希望对你有帮助。