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

Oracle临时表游标未释放导致回滚段空间不足时会报出多个ORA-01650错误，造成数据库无法运行。本文我们主要就介绍了这一问题的解决方案，接下来就让我们来一起了解一下这部分内容。 先分析一下相关背景。产生报错的程序是一个数据处理模块，每天会将其他系统传过来的平面文件中的内容处理后放入数据库中，事务量很大。我们的系统是9i，划分了16个回滚段，其中两个大的batch回滚段，每个batch回滚段有6G的足够空间。而数据处理模块会在事务中指定使用BATCH1。 再分析报错的模块。检查相关的数据事务处理部分，由于业务需要保持数据的一致性，需要处理完1个文件后才能提交，中间如果出错就要全部回滚。经过确认，这部分代码有很长时间没有做改动了。然后再确认数据量，可以确认，今天（周五）是一周之内文件内容最少的一天。也就是说，如果由于数据量引起错误，其他时间的概率应该更大。 但是，有一点需要注意。指定回滚段是针对事务的，不是针对回滚段。也就是说，我们可以指定某个事务只使用某个回滚段，但是不能保证这个回滚段只被这个事务事务。当一个事务申请使用回滚段时，如果没有自己指定，oracle就会根据当时的各个回滚段的使用情况，分配一个最合适的回滚段给这个事务使用。因此，报回滚段空间不足的事务可能不一定就是导致回滚段空间不足的事务。还有一种可能就是，事务所指定的回滚段被其他事务所占用了。 于是我们就检查是否还有其他事务占用了该回滚段。 SELECT?s.sid,?s.username,?s.osuser,?s.machine,?s.program, ? t.xidusn,t.ubafil,t.ubablk,t.used_ublk,?t.log_io,?t.phy_io,?t.cr_get,?t.cr_change, ? r.name,?q.sql_text ? FROM?v$session?s,v$transaction?t,?v$RollName?r,?v$sqlarea?q ? WHERE?s.saddr t.ses_addr ? and?t.xidusn? ?r.usn ? and?s.sql_address? ?q.address + and?s.sql_hash_value? ?q.hash_value + And?r,name? ?RBS_BATCH1;? 果然发现有5个事务在占用BATCH1。但是发现会话状态为INACTIVE。这说明它们当时并没有运行INSERT/UPDATE/DELETE语句（曾经运行过，事务没有结束），而是将回滚段资源hung住了。 再查下那些对象被hung在BATCH1中， 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, ? t.log_io,?t.phy_io,?t.cr_get,?t.cr_change,?r.name ? from?v$locked_object?l,?dba_objects?o,?v$transaction?t,?v$RollName?r ? where?l.object_id? ?o.object_id ? and?l.xidusn? ?t.xidusn ? and?l.xidslot? ?t.xidslot ? and?l.xidsqn? ?t.xidsqn ? and?t.xidusn? ?r.usn;? 嗯，都是同一个对象：“TMP_CNT_GRP”。通过SID确认，确实上面的事务都是将这个对象hung在RBS中的。 经过检查，原来这个对象是一个临时表。我们知道，临时表对象平时是不存在数据的。只有当一个会话使用临时表，并向表中插入数据后，oracle才会在临时表空间上创建它的数据对象。临时表数据之所以只被所调用会话看到，是实际上是在每个会话中创建了一个单独的数据对象，有各自的数据对象标号。因此尽管是同一个临时表，每个会话只是copy一个表结构，而创建了不同的数据对象，这样，会话之间就不会有数据干扰。而在一个会话中，对临时表数据对象的处理跟普通数据对象处理基本相同，其中就包括临时表对象在事务中的数据改动也会有回滚信息的产生。 回到我们的问题中。通过V$SESSION和V$SQL_AREA查到，这些会话都是调用了一个PLSQL函数，而且都是通过java调用的。 Review代码，终于发现潜在问题了：这个函数的结果是返回一个游标，而游标恰恰关联了这张临时表。 INSERT?INTO?TMP_CNT_GRP?... SELECT......?... open?v_cursor?FO