Oracle临时表游标未释放导致回滚段空间不足的解决方案.doc

《Oracle临时表游标未释放导致回滚段空间不足的解决方案.doc》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Oracle临时表游标未释放导致回滚段空间不足时会报出多个ORA-01650错误，造成数据库无法运行。本文我们主要就介绍了这一问题的解决方案，接下来就让我们来一起了解一下这部分内容。先分析一下相关背景。产生报错的程序是一个数据处理模块，每天会将其他系统传过来的平面文件中的内容处理后放入数据库中，事务量很大。我们的系统是9i，划分了16个回滚段，其中两个大的batch回滚段，每个batch回滚段有6G的足够空间。而数据处理模块会在事务中指定使用BATCH1。再分析报错的模块。检查相关的数据事务处理部分，由于业务需要保持数据的一致性，需要处理完1个文件

2、后才能提交，中间如果出错就要全部回滚。经过确认，这部分代码有很长时间没有做改动了。然后再确认数据量，可以确认，今天（周五）是一周之内文件内容最少的一天。也就是说，如果由于数据量引起错误，其他时间的概率应该更大。但是，有一点需要注意。指定回滚段是针对事务的，不是针对回滚段。也就是说，我们可以指定某个事务只使用某个回滚段，但是不能保证这个回滚段只被这个事务事务。当一个事务申请使用回滚段时，如果没有自己指定，oracle就会根据当时的各个回滚段的使用情况，分配一个最合适的回滚段给这个事务使用。因此，报回滚段空间不足的事务可能不一定就是导致回滚段空间不足的

3、事务。还有一种可能就是，事务所指定的回滚段被其他事务所占用了。于是我们就检查是否还有其他事务占用了该回滚段。1.SELECT s.sid, s.username, s.osuser, s.machine, s.program, 2.t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.log_io, t.phy_io, t.cr_get, t.cr_change, 3.r.name, q.sql_text 4.FROM v$session s,v$transaction t, v$RollName r, v$sqlar

4、ea q 5.WHERE s.saddr=t.ses_addr 6.and t.xidusn = r.usn 7.and s.sql_address = q.address(+) 8.and s.sql_hash_value = q.hash_value(+)And r,name = 'RBS_BATCH1'; 果然发现有5个事务在占用BATCH1。但是发现会话状态为INACTIVE。这说明它们当时并没有运行INSERT/UPDATE/DELETE语句（曾经运行过，事务没有结束），而是将回滚段资源hung住了。再查下那些对象被hung在BATCH1

5、中，1.select l.session_id, l.os_user_name, l.oracle_username, o.owner, o.object_name, t.xidusn,t.ubafil,t.ubablk,t.used_ublk, t.used_urec, 2.t.log_io, t.phy_io, t.cr_get, t.cr_change, r.name 3.from v$locked_object l, dba_objects o, v$transaction t, v$RollName r 4.where l.object_

6、id = o.object_id 5.and l.xidusn = t.xidusn 6.and l.xidslot = t.xidslot 7.and l.xidsqn = t.xidsqn 8.and t.xidusn = r.usn; 嗯，都是同一个对象：“TMP_CNT_GRP”。通过SID确认，确实上面的事务都是将这个对象hung在RBS中的。经过检查，原来这个对象是一个临时表。我们知道，临时表对象平时是不存在数据的。只有当一个会话使用临时表，并向表中插入数据后，oracle才会在临时表空间上创建它的数据对象。临时表数据之所以只被所调用会

7、话看到，是实际上是在每个会话中创建了一个单独的数据对象，有各自的数据对象标号。因此尽管是同一个临时表，每个会话只是copy一个表结构，而创建了不同的数据对象，这样，会话之间就不会有数据干扰。而在一个会话中，对临时表数据对象的处理跟普通数据对象处理基本相同，其中就包括临时表对象在事务中的数据改动也会有回滚信息的产生。回到我们的问题中。通过V$SESSION和V$SQL_AREA查到，这些会话都是调用了一个PLSQL函数，而且都是通过java调用的。Review代码，终于发现潜在问题了：这个函数的结果是返回一个游标，而游标恰恰关联了这张临时表。1.IN

8、SERT INTO TMP_CNT_GRP ... 2.    SELECT...... ...      open v_