减小删除查询的大小是否是避免ORA-01628的正确方法? [英] Is decreasing size of delete query a correct way to avoid ORA-01628?

问题描述

我有一个删除查询，例如:

I have a delete query like:

delete from table1 where dataA between Date1 and Date2

我收到一个错误:

Caused by: java.sql.SQLException: ORA-01628: max # extents (10100) reached 
for rollback segment.

我想通过添加

AND ROWNUM < 200 

这是解决问题的正确方法吗(表包含blob列，因此，我认为回滚段很小)?

Is this the correct way to resolve the problem (table contains blob columns — for this reason I suppose rollback segment is small)?

推荐答案

我在Oracle中进行数据仓库批量处理，您的方法非常好.我想告诉dba嘿，添加大量的rollback/tmp空间，这样我们就再也不会遇到这个问题了……但是与许多客户一样，资源很多次都超出了我们的控制范围.

I do data warehouse bulk processing in Oracle and your method is perfectly fine. I would like to tell the dba to say hey add a ton of rollback/tmp space so we never run into this... but as with many clients... resources are many times outside of our control.

以下是我要在大块中删除的任何重复删除操作中的操作(Oracle Pl/sql示例，但将移植到任何语言)请记住，此解决方案仅适用于无条件删除表达式(您的示例)...如果您遇到where条件，建议您首先批量获取rowid/keys到数组中，然后循环删除大块中的代码...

What i do in any repetitive delete operation that you just want to delete in chunks is the following (Oracle Pl/sql example but will port to any language) Keep in mind this solution is only for a delete from without conditional where expression(your example)... if you had a where condition i would recommend fetching rowid's/keys into array first in bulk and the looping deleting in chunks...

//psuedo code
Create procedure (iCommit In Integer) 
   Loop
      Delete From Table where rownum < iCommit + 1;
      if SQL%ROWCOUNT < iCommit then
         exit loop;
      end if
      // incremental commit
      commit;
   End Loop;
   // final commit
   commit;
)

这篇关于减小删除查询的大小是否是避免ORA-01628的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！