异常ORA-08103：使用Hirabenate的setfetchsize时不再存在对象

问题描述

我正在使用Hibernate。我需要抓取1000000条记录，这将导致超时异常。所以我正在使用 setfetchsize 获取6000条记录，以便它可以在6000个记录中分配多个交易操作。

但是，如果有人删除要获取的记录之一，那么我将获得 ORA-08103：对象不再存在。

现在我想跳过在检索时被删除的对象。如何做到这一点？

解决方案

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

  SQL>声明
 2类型t_recs是数字表; 
 3 l_cur sys_refcursor; - 我们的游标
 4 l_rec t_recs; 
 5 
 6开始
 7 
 8  - 填充全局临时表GTT1与样本数据
 9插入GTT1（col）
 10选择级别
 11 from dual 
 12 connect by level< = 1000; 
 13 
 14 open l_cur  - 根据GTT1的数据打开一个游标
 15为GTT1选择col 
 16; 
 17 
 18  - 这里删除语句
 19  - 和
 20 commit; <  - 导致错误。提交GTT1之后的所有数据将被
 21删除，当我们尝试从光标中获取
 22循环时 - 我们将面临ORA-08103错误
 23 fetch l_cur  -  - 尝试提取已经很久的数据。 
 24批量收集到l_rec; 
 25出现时l_cur％notfound; 
 26 end loop; 
 27 
 28 end; 
 29 / 
 
 
 ORA-08103：对象不再存在
 ORA-06512：第24行
  pre> 
 
 
在提交保留行子句上使用创建全局临时表的条件将允许从游标中安全地获取数据，是基于该表而不怕面对 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：使用Hirabenate的setfetchsize时不再存在对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！