最近在制作现网报表数据时发觉alert.log中抛如下的异常:"Failure to extend rollback segment 24 because of 30036 conditionFULL status of rollback segment 24 set."

这是由于报表数据生成过程中有大量的中间临时数据insert和update,导致回滚空间爆涨,不能及时扩展新的extent.此时有效的解决方案有:1.改进报表数据的生成语句,将大事务化为小事务,多加些commit;2.将部分update语句整合(期间我新加了索引,极大提高报表生成速度);3.及时给回滚表空间添加新的数据文件或resize部分文件;4.新建专有的回滚表空间来对付报表数据的生成.

这里我将新建一回滚表空间,同时切换系统的回滚表空间,期间的一些操作整理如下:

create undo tablespace undotbs2 datafile '/eva2/ora9i/undo1.dbf' size 300M;

ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;

drop tablespace UNDOTBS1 including contents and datafiles;(抛异常,因为有active的回滚段)

alert.log中一直抛如下的信息:

Thu Mar 1 17:50:26 2007
Undo Tablespace 1 moved to Pending Switch-Out state.
Thu Mar 1 17:55:26 2007
Undo Tablespace 1 moved to Pending Switch-Out state.
Undo Tablespace 1 successfully switched out.

如下查询UNDOTBS1下的回滚段信息:

SQL> select * from dba_undo_extents where tablespace_name='UNDOTBS1' and status='ACTIVE';

OWNER SEGMENT_NAME TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO COMMIT_JTIME COMMIT_WTIME STATUS
----- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ ------------ -------------------- ---------
SYS _SYSSMU10$ UNDOTBS1 0 211 131066 57344 7 211 ACTIVE
SYS _SYSSMU10$ UNDOTBS1 2 211 16265 1048576 128 211 ACTIVE

SQL>

SQL> select * from dba_rollback_segs where tablespace_name='UNDOTBS1' and STATUS = 'ONLINE';

SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE STATUS INSTANCE_NUM RELATIVE_FNO
------------------------------ ------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------------- ---------------------------------------- ------------
_SYSSMU10$ PUBLIC UNDOTBS1 10 211 131065 131072 2 32765 ONLINE 211

SQL>

发现_SYSSMU10$回滚段一直在使用中.

紧接着查看当前那些session正在使用该回滚段,见如下查询:

SQL> SELECT r.name "回滚段名",s.sid,s.serial#,s.username "用户名",t.status,
2 　　 t.cr_get,t.phy_io,t.used_ublk,t.noundo,substr(s.program, 1, 78) "操作程序"
3 FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r
4 WHERE t.addr = s.taddr and t.xidusn = r.usn
5 ORDER BY t.cr_get,t.phy_io
6 /

回滚段名 SID SERIAL# 用户名 STATUS CR_GET PHY_IO USED_UBLK NOUNDO 操作程序
------------------------------ ---------- ---------- ------------------------------ ---------------- ---------- ---------- ---------- ------ --------------------------------------------------------------------------------
_SYSSMU21$ 238 34291 PONA ACTIVE 4 1 1 NO plsqldev.exe
_SYSSMU10$ 234 57017 CEMMARS ACTIVE 9651 117 1 NO
_SYSSMU10$ 450 12091 CEMBSB ACTIVE 9651 117 1 NO oracle@HP380 (TNS V1-V3)

有了sid和serial#,将session给kill掉

SQL> alter system kill session '234,57017' immediate;

System altered

SQL> alter system kill session '450,12091' immediate;

System altered

SQL>
立马在日志中出现如下的信息

Thu Mar 1 17:50:26 2007
Undo Tablespace 1 moved to Pending Switch-Out state.
Thu Mar 1 17:55:26 2007
Undo Tablespace 1 moved to Pending Switch-Out state.
Undo Tablespace 1 successfully switched out.
此时再执行drop tablespace UNDOTBS1 including contents and datafiles;成功

[@more@]