临时表游标未释放导致回滚段空间不足案例
[English]
作者：
fuyuncat
来源：
www.HelloDBA.com
分享到
一大早，突然收到生产系统报出多个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 FOR select TMP.CDE, CAR.ID, CAR.NME, COUNT(DISTINCT TMP.NUM) TOTAL_CNT from TMP_CNT_GRP TMP, CSS_CAR CAR WHERE TMP.ID = CAR.ID GROUP BY TMP.CDE, CAR.ID, CAR.NME;
RETURN v_cursor;
DELETE TMP_CNT_GRP;
(这段代码其实还存在一个问题，也就是最后的DELETE语句根本不会被调用)
从这段代码中可以看到，实际上在整个函数当中，临时表的数据根本不会被释放；而且也没有提交和回滚事务（尽管这是一个会话级的临时表）。占用的回滚段也不会被释放。这就存在这样的潜在问题，如果调用者不关闭会话或提交/回滚事务的话，它所占用的回滚段就不会被释放。事实上，经过让java开发人员检查代码，果然发现客户端在打开会话后，就没有关闭，知道客户端本身结束。
最后的解决办法：
1、因为这是一个会话级的临时表，数据在事务提交后继续保留，因此在PLSQL函数中的insert语句后加上commit；
2、Java代码在使用完游标后关闭会话。