异常ORA-08103：在使用Hibernate的setfetchsize时不再存在对象 [英] Exception ORA-08103: object no longer exists on using setfetchsize of Hibernate

问题描述

我正在使用Hibernate。我需要获取大约1000000条记录，它会导致超时异常。因此，我对6000条记录使用 setfetchsize ，以便它将分布在6000条记录的多个交易中。

但是同时检索记录是否有人删除了要提取的记录之一，然后得到 ORA-08103：对象不再存在。

现在我想跳过检索时删除的那个对象。我该怎么做？

解决方案

很可能游标是基于全局临时表（GTT）打开的，已使用 ON COMMIT DELETE ROWS 选项创建。并且 ORA-08103：对象的原因不再存在错误是 commit 语句紧随在 delete 语句。这里有一个简单的例子：

  SQL>申报
 2类型t_recs是数字表格; 
 3 l_cur sys_refcursor; - 我们的游标
 4 l_rec t_recs; 
 5 
 6 begin 
 7 
 8  - 用示例数据填充全局临时表GTT1 
 9插入GTT1（col）
 10选择级别
 11来自双
 12按等级连接< = 1000; 
 13 
 14 open l_cur  - 根据来自GTT1的数据打开游标
 15从GTT1中选择col 
 16; 
 17 
 18  - 这里是删除语句
 19  - 和
 20提交; <  - 导致错误。提交GTT1中的所有数据后，将
 21  - 删除，当我们试图从光标
 22循环中获取数据时 - 我们将面对ORA-08103错误
 23 fetch l_cur  -  - 尝试获取已消失的数据。 
 24批量收入l_rec; 
 25当l_cur％notfound时退出; 
 26结束循环; 
 27 
 28 end; 
 29 / 
 
 
 ORA-08103：对象不再存在
 ORA-06512：在第24行
  

使用提交保留行子句的全局临时表可以安全地从游标中获取数据是基于该表而不会害怕面对 ORA-08103：错误。

I'm using Hibernate. I need to fetch around 1000000 records and it will cause timeout exception. So I'm using setfetchsize for 6000 records, so that it will distribute the operation in multiple transactions each of 6000 records.

It will take around 21 hours to fetch all.

But meanwhile retrieving records if somebody deletes one of the record which was to be fetched then I get ORA-08103: object no longer exists.

Now I want to skip that object which is deleted while retrieving. How can I do this?

解决方案

Most likely that a cursor is opened based on a global temporary table(GTT), which had been created with ON COMMIT DELETE ROWS option. And the cause of the ORA-08103: object no longer exists error is commit statement that followed right after the delete statement. Here is a simple example:

 SQL> declare
  2    type t_recs is table of number;
  3    l_cur sys_refcursor;    -- our cursor
  4    l_rec t_recs; 
  5  
  6  begin
  7  
  8    -- populating a global temporary table GTT1 with sample data  
  9    insert into GTT1(col)
 10      select level
 11        from dual
 12     connect by level <= 1000;
 13  
 14   open l_cur         -- open a cursor based on data from GTT1
 15    for select col
 16          from GTT1;
 17  
 18    -- here goes delete statement
 19    -- and
 20    commit;  <-- cause of the error. After committing  all data from GTT1 will be
 21              -- deleted and when we try to fetch from the cursor
 22    loop      -- we'll face the ORA-08103 error
 23      fetch l_cur    -- attempt to fetch data which are long gone.
 24       bulk collect into l_rec;
 25      exit when l_cur%notfound;
 26    end loop;
 27  
 28  end;
 29  /


ORA-08103: object no longer exists
ORA-06512: at line 24

Recreation of global temporary table with on commit preserve rows clause will allow to safely fetch data from a cursor that is based on that table without being afraid of facing ORA-08103: error.

这篇关于异常ORA-08103：在使用Hibernate的setfetchsize时不再存在对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！