ORA-14551: 无法在查询中执行 DML 操作

本文探讨了在Oracle中遇到无法在查询中执行DML操作的问题,并介绍了如何通过添加PRAGMA AUTONOMOUS_TRANSACTION语句来解决该问题,解释了自治事务与主事务的区别及应用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

最近在调试一个带DML操作的函数时,一直不成功,在PL/SQL中测试时没问题,通过 SQL语句调用函数时就不行了,刚开始一直没找到原因,后来无意间把 函数中捕获异常的代码注释掉,终于通过SQL调试时,弹出了一个“ORA-14551: 无法在查询中执行 DML 操作 .”错误,找到了问题原因,就好找解决办法了,在网上找到一篇文章,大谈什么自治事务和主事务,看了半天,还是云里雾里,找到关键点,就是添加一条语句“PRAGMA AUTONOMOUS_TRANSACTION;”,并在最后 COMMIT 提交DML操作,问题迎刃而解,至于这个什么自治事务和主事务,还是有时间,后面再慢慢消化了。

----以下是引用文章:

在函数中,往临时表插入数据报错:

ORA-14551: 无法在查询中执行 DML 操作

ORA-06512: 在 "NSTCSA.NS_ST_GETRAISEFUNDX", line 29

 

增加下面的字符串:

PRAGMA AUTONOMOUS_TRANSACTION;

 

 

数据库事务是一种单元操作,要么是全部操作都成功,要么全部失败。在Oracle中,一个事务是从执行第一个数据管理语言(DML)语句开始,直到执行一个COMMIT语句,提交保存这个事务,或者执行一个ROLLBACK语句,放弃此次操作结束。

 

事务的“要么全部完成,要么什么都没完成”的本性会使将错误信息记入数据库表中变得很困难,因为当事务失败重新运行时,用来编写日志条目的INSERT语句还未完成。

 

针对这种困境,Oracle提供了一种便捷的方法,即自治事务。自治事务从当前事务开始,在其自身的语境中执行。它们能独立地被提交或重新运行,而不影响 正在运行的事务。正因为这样,它们成了编写错误日志表格的理想形式。在事务中检测到错误时,您可以在错误日志表格中插入一行并提交它,然后在不丢失这次插 入的情况下回滚主事务。

 

因为自治事务是与主事务相分离的,所以它不能检测到被修改过的行的当前状态。这就好像在主事务提交之前,它们一直处于单独的会话里,对自治事务来说,它们是不可用的。然而,反过来情况就不同了:主事务能够检测到已经执行过的自治事务的结果。

 

要创建一个自治事务,您必须在匿名块的最高层或者存储过程、函数、数据包或触发的定义部分中,使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION语句。在这样的模块或过程中执行的SQL Server语句都是自治的。

 

触发无法包含COMMIT语句,除非有PRAGMA AUTONOMOUS_TRANSACTION标记。但是,只有触发中的语句才能被提交,主事务则不行。

exp:

Create table Msg (Msg varchar(50)) ;
自制事务:
create or replace procedure AutoNomouse_Insert is
    PRAGMA AUTONOMOUS_TRANSACTION;
     begin
             insert into Msg values('AutoNomouse Insert');
            commit;
    end;
非自治事务:
CREATE OR REPLACE Procedure NonAutoNomouse_Insert as
    begin
           insert into Msg Values('NonAutonomouse Insert');
           commit;
    end;

SQL> begin
  2 
  3            insert into Msg Values('This Main Info');
  4 
  5            NonAutoNomouse_Insert;
  6 
  7            rollback;
  8 
  9  end
 10  ;
 11  /
 
PL/SQL procedure successfully completed
 
SQL> select * from msg;
 
MSG
--------------------------------------------------
This Main Info
NonAutonomouse Insert
因为过程中有COMMIT;所以匿名块中得RULLBACK 是不起作用的; 由此得出:非自治事务中的COMMIT,ROLLBACK
是会影响整个事务的。
下面我们看一个另外一种情况:
SQL> delete msg;
 
2 rows deleted
 
SQL>
 
这里没有COMMIT;

SQL> begin
  2 
  3            insert into Msg Values('This Main Info');
  4 
  5            rollback;  --这里加了ROLLBACK;
  6 
  7            NonAutoNomouse_Insert;
  8 
  9            rollback;
 10 
 11  end
 12  ;
 13  /
 
PL/SQL procedure successfully completed
 
SQL> select * from msg;
 
MSG
--------------------------------------------------
This Main Info
NonAutonomouse Insert
NonAutonomouse Insert
 
竟然没有ROLLBACK (DELETE * FROM SSG ;) 为什么了?因为过程就是一个新的SESSION,所以前面的SESSION
被正常EXIT,同时被自动提交; 所以我们会看到三行数据。
SQL> commit;
 
Commit complete
 
SQL> select * from msg;
 
MSG
--------------------------------------------------
This Main Info
NonAutonomouse Insert
NonAutonomouse Insert
 
SQL> commit;
 
Commit complete
 
SQL> select * from msg;
 
MSG
--------------------------------------------------
This Main Info
NonAutonomouse Insert
NonAutonomouse Insert
因为这里一个新的SESSION 所以是没有意义的事务控制语句。
 
SQL> delete msg;
 
3 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> select * from msg;
 
MSG
--------------------------------------------------
可以看到这里是正常的提交;
 
下面看一下自制事务:
SQL> begin
  2 
  3            insert into Msg Values('This Main Info');
  4 
  5            AutoNomouse_Insert;
  6 
  7            rollback;
  8 
  9  end
 10 
 11  ;
 12  /
 
PL/SQL procedure successfully completed
 
SQL> select * from msg;
 
MSG
--------------------------------------------------
AutoNomouse Insert
 
我们看到是一行数据,显然第一条SQL INSERT 是被ROLLBACK,证明自制事务是一个独立于主程序的事务,
他不会对主事务的控制产生影响。另外在分布式环境中我们经常会遇到 ORA-02064 ERROR ,就是因为主事务
自己有事务控制语句,然而被调用的远程过程也有自己的事物控制语句,当然就会报错,我们将被调用的过程
声明为自制事务那就OK了。
 

今天在函数中使用insert语句时,报了ora-14551 无法在查询中执行dml操作错误。

错误的解决办法,有两种:

一、在函数外面套存储过程;

二、使用自治事务(AUTONOMOUS TRANSACTION)

在函数声明部分加入这句话

PRAGMA AUTONOMOUS_TRANSACTION;

我选择了后一种。

<think>我们正在处理一个Oracle数据库错误:ORA-12801,它发生在并行查询服务器中。根据提供的引用信息,我们有两个引用: 引用[1]:错误信息为ORA-12801,并指出在并行查询服务器P02F上发生了错误,同时伴随有ORA-12829错误(死锁,在文件139的块16320处,itls被兄弟事务占用)。 引用[2]:同样提到了ORA-12801错误,并给出了一段SQL片段(select min(d.date_dim_id)...),这可能是触发错误的SQL语句的一部分。 用户的问题:我想查找并解决Oracle数据库并行查询服务器错误ORA-12801。请问ORA-12801: 并行查询服务器 P100 错误原因及解决方案。 根据Oracle官方文档,ORA-12801是一个并行查询相关的错误,通常表示在并行查询服务器进程中发生了内部错误。具体到错误信息中的P100,它指的是一个特定的并行查询服务器进程(P100)。而引用中提到的ORA-12829则是一个死锁错误,与ITL(事务槽)有关。 分析可能的原因: 1. **并行查询中的死锁问题**:引用[1]中提到了ORA-12829,这是一个死锁错误,发生在块级。在Oracle中,当多个并行服务器进程尝试同时修改同一个数据块时,可能会发生ITL(Interested Transaction List)争用,导致死锁。ITL用于记录哪些事务正在使用该数据块。如果事务过多,或者块上的ITL槽不足,就可能出现这种问题。 2. **并行查询的配置问题**:并行查询的配置不当(如并行度设置过高)可能导致资源争用加剧,从而引发死锁。 3. **SQL语句问题**:引用[2]中提供了一个SQL片段,其中涉及到了子查询。复杂的SQL语句在并行执行时可能更容易引发资源争用。 解决方案: 1. **检查具体错误细节**:首先需要查看详细的跟踪文件和日志,确定错误发生的具体位置和SQL语句。错误信息中提到的文件号和块号(文件139,块16320)可以帮助定位问题对象。 2. **调整ITL设置**:如果错误是由ITL争用引起的,可以考虑增加数据块的ITL槽数量。这可以通过修改表的存储参数`INITRANS`和`MAXTRANS`(注意:在较高版本的Oracle中,MAXTRANS已被忽略,通常只设置INITRANS)来实现。例如: ```sql ALTER TABLE table_name STORAGE (INITRANS n); ``` 其中`n`是一个比当前值更大的数(例如,从默认的1或2增加到10或更多)。但要注意,增加INITRANS可能会增加块头的大小,从而略微增加存储开销。 3. **减少并行度**:降低并行查询的并行度(DOP),以减少同时访问相同数据块的并行服务器进程数量。可以通过在SQL语句中使用提示来指定较低的并行度,例如: ```sql SELECT /*+ PARALLEL(table_name, 4) */ ... ``` 或者修改表或索引的并行度: ```sql ALTER TABLE table_name PARALLEL 4; ``` 4. **避免并行DML**:如果错误发生在并行DML(如INSERT、UPDATE、DELETE)操作中,考虑是否可以不使用并行DML,或者将操作分解为多个串行操作。 5. **优化SQL语句**:检查导致错误的SQL语句,看是否可以优化以减少对同一数据块的争用。例如,避免全表扫描,使用索引扫描等。 6. **应用补丁**:有时,这类错误可能是Oracle的bug,需要检查Oracle的版本,并查看是否有相关的补丁。可以查询My Oracle Support(MOS)网站,查找与ORA-12801和ORA-12829相关的bug和补丁。 具体步骤建议: 1. 收集信息:获取完整的错误日志(包括跟踪文件),确定是哪个SQL语句导致的错误,以及错误发生的具体对象(通过文件号和块号可以查询到是哪个段)。 ```sql SELECT tablespace_name, segment_name, segment_type, partition_name FROM dba_extents WHERE file_id = 139 AND 16320 BETWEEN block_id AND block_id + blocks - 1; ``` 2. 如果确认是ITL问题,尝试增加该段的INITRANS(并可能需要重新组织数据,比如移动表,使新参数生效)。 3. 降低该SQL语句的并行度,观察是否解决问题。 4. 如果问题仍然存在,考虑在MOS上搜索具体的错误号(ORA-12801和ORA-12829)以及Oracle版本,看是否有已知的bug。 注意:在修改任何参数之前,请在测试环境中验证,并备份相关数据。 相关问题: 1. 如何查询Oracle数据库中数据块对应的段对象? 2. 如何调整表的INITRANS参数? 3. 如何修改SQL语句的并行度? 4. 如何查询Oracle的bug和补丁信息?</think>### ORA-12801: 并行查询服务器错误分析与解决方案 #### 错误原因分析 根据引用内容[^1][^2],ORA-12801是Oracle并行查询服务器进程(如P100)发生的内部错误,常伴随其他错误代码(如ORA-12829)。核心原因包括: 1. **ITL死锁问题** ORA-12829表明发生"兄弟事务死锁"(Deadlock - itls occupied by siblings),这是**索引块事务槽(ITL)争用**导致的[^1]。当多个并行进程同时修改同一索引块时,ITL槽不足会引发死锁。 2. **并行查询资源冲突** 引用[^2]显示并行查询服务器(P002)在执行复杂SQL(如子查询`min(d.date_dim_id)...`)时触发了错误,常见场景: - 高并发DML操作(INSERT/UPDATE/DELETE) - 不合理的并行度(DOP)设置 - 索引块热点争用 3. **硬件/存储层问题** 错误指向具体数据块(如文件139的块16320[^1]),可能涉及: - 存储I/O异常 - 坏块(Corrupt Blocks) - ASM磁盘组故障 --- #### 解决方案 ##### 紧急处理 ```sql -- 1. 立即终止问题会话 ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; -- 2. 检查坏块(需DBA权限) SELECT * FROM V$DATABASE_BLOCK_CORRUPTION WHERE file#=139 AND block#=16320; -- 替换错误中的文件/块号[^1] ``` ##### 根本性修复 **1. 解决ITL死锁** ```sql -- 增加表/索引的INITRANS(事务槽数量) ALTER INDEX <index_name> STORAGE (INITRANS 10); -- 默认值为2,建议增至5-10 -- 重建热点索引(适用于频繁修改的索引) ALTER INDEX <index_name> REBUILD; ``` **2. 优化并行查询** ```sql -- 降低并行度(DOP) ALTER SESSION FORCE PARALLEL DML PARALLEL 4; -- 根据CPU核数合理设置 -- SQL提示指定串行执行(临时方案) SELECT /*+ NO_PARALLEL */ ... FROM ... -- 在问题SQL中添加提示 ``` **3. 存储层检查** ```bash # 使用DBV验证数据文件完整性 dbv file=<datafile_path> blocksize=8192 ``` **4. 应用层修改** - 拆分大事务为小批次提交 - 避免高频更新索引前导列 - 检查子查询逻辑(如引用[^2]中的`min(d.date_dim_id)`) --- #### 预防措施 1. **监控ITL使用** ```sql SELECT table_name, ini_trans, max_trans FROM dba_tables WHERE table_name='<TABLE_NAME>'; ``` 2. **调整表空间参数** ```sql ALTER TABLESPACE <tbs_name> DEFAULT STORAGE (INITRANS 6); ``` 3. **定期重建索引** ```sql -- 针对修改频繁的表 ALTER INDEX <index_name> REBUILD ONLINE; ``` > **关键提示**:若问题持续,检查Oracle Metalink是否有相关补丁(Bug号通常与ORA-12801关联),并验证存储阵列的I/O延迟[^1][^2]。 --- ### 相关问题 1. 如何诊断Oracle并行查询的性能瓶颈? 2. ITL死锁与普通行级死锁有何本质区别? 3. 哪些场景下需要增加数据库块的INITRANS值? 4. 如何监控Oracle数据库中的坏块? 5. 并行查询中如何合理设置DOP(并行度)参数? [^1]: 引用[1]: ORA-12801: error signaled in parallel query server P02F... ORA-12829: Deadlock - itls occupied by siblings at block 16320 of file 139 [^2]: 引用[2]: ora_p002_oracle,ORA-12801: error signaled in parallel query server...(select min(d.date_dim_id)...
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值