产生问题的原因主要以下两点：
1. 有较大的事务量让oracle undo自动扩展，产生过度占用磁盘空间的情况；
2. 有较大事务没有收缩或者没有提交所导制；
说明：本问题在oracle系统管理中属于比较正常的一现象，日常维护多注意对磁盘空间的监控。
undo表空间介绍
undo表空间用于存放undo数据,当执行dml操作(insert,update和delete)时,oracle会将这些操作的旧数据写入到undo段,在oracle9i之前,管理undo数据时使用(rollback segment)完成的.从oracle9i开始,管理undo数据不仅可以使用回滚段,还可以使用undo表空间.因为规划和管理回滚段比较复杂,所有oracle database 10g已经完全丢弃用回滚段.并且使用undo表空间来管理undo数据。
1、查看系统磁盘状态
aix系统:/> df -g (linux系统: df -h)
filesystem gb blocks free %used iused %iused mounted on
/dev/undolv 30.00 0.00 100% 9 1% /u01/app/u01/app/oracle/undo
2、查看oracle数据库表空间的占有率
select a.tablespace_name,
round((a.maxbytes / 1024 / 1024), 2) sum mb,
round((a.bytes / 1024 / 1024), 2) datafile mb,
round(((a.bytes - b.bytes) / 1024 / 1024), 2) used mb,
round(( (a.maxbytes-a.bytes+b.bytes) / 1024 / 1024), 2) free mb,
round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) percent_used
from (select tablespace_name, sum(bytes) bytes,sum(maxbytes) maxbytes
from dba_data_files where maxbytes!=0
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.maxbytes) desc
tablespace_name sumdatafile(mb) datafile used free precent_used
1 undotbs1 32767.98 30000 29968 2799.98 91.46
或者通过如下脚本检查数据库表空间占用空间情况:
select tablespace_name,sum(bytes)/1024/1024/1024 gb
from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 gb
from dba_temp_files group by tablespace_name order by gb;
3、找出undo表空间的路径及大小
sql> select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name like 'undotbs1'
/u01/app/oracle/undo/undotbs01.dbf 30000
4、检查undo segment状态
sql> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
from v$rollstat order by rssize;
usn xacts rssize/1024/1024/1024 hwmsize/1024/1024/1024 shrinks
1 0 0 0.000358582 0.000358582 0
2 14 0 0.796791077 0.796791077 735
3 13 0 0.800453186 0.800453186 894
4 12 0 0.805213928 0.805213928 728
5 15 0 1.186126709 1.186126709 922
6 1 0 1.723365784 1.963180542 946
7 3 0 1.732704163 1.977462769 1051
8 5 0 1.978370667 2.228370667 654
9 2 0 2.032501221 2.034454346 707
10 4 0 2.065216064 2.318145752 875
11 11 0 2.100006104 2.100006104 1269
12 8 0 2.630340576 2.700653076 897
13 6 0 2.740814209 2.740814209 1030
14 9 0 2.745697021 2.772064209 1037
15 7 0 2.833526611 2.833526611 1033
16 10 0 3.088363647 3.310592651 989
这还原表空间中还存在16个回滚的对象。
5、创建新的临时undo表空间
可以在其它的磁盘空间临时创建还原表空间
sql>
create undo tablespace undotbs2
datafile '/u01/app/oracle/pub/undotbs02.dbf'
size 10m autoextend on;
tablespace created.
6、切换undo表空间为新的undo表空间
sql> alter system set undo_tablespace=undotbs2 scope=both;
system altered.
7、验证当前数据库的还原表空间
sql> show parameter undo
name type value
------------------------------------ ----------- --------------
undo_management string auto
undo_retention integer 900
undo_tablespace string undotbs2
8、等待原undo表空间所有undo segment offline
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
segment_name tablespace_name segment_id status
1 system system 0 online
2 _syssmu1$ undotbs1 1 offline
3 _syssmu2$ undotbs1 2 offline
48 _syssmu47$ undotbs1 47 offline
49 _syssmu48$ undotbs1 48 offline
50 _syssmu49$ undotbs1 49 offline
51 _syssmu50$ undotbs1 50 offline
52 _syssmu51$ undotbs1 51 offline
53 _syssmu52$ undotbs1 52 offline
54 _syssmu53$ undotbs1 53 offline
55 _syssmu54$ undotbs1 54 offline
56 _syssmu55$ undotbs1 55 offline
57 _syssmu56$ undotbs1 56 offline
58 _syssmu57$ undotbs1 57 offline
59 _syssmu58$ undotbs1 58 offline
60 _syssmu59$ undotbs1 59 offline
61 _syssmu60$ undotbs1 60 offline
62 _syssmu61$ undotbs1 61 offline
63 _syssmu62$ undotbs2 62 online
64 _syssmu63$ undotbs2 63 online
65 _syssmu64$ undotbs2 64 online
66 _syssmu65$ undotbs2 65 online
67 _syssmu66$ undotbs2 66 online
68 _syssmu67$ undotbs2 67 online
69 _syssmu68$ undotbs2 68 online
上面对应的undotbs1还原表空间所对应的回滚段均为offline
9、删除原undo表空间
sql> drop tablespace undotbs1 including contents and datafiles;
tablespace dropped.
10、可以再次查看系统磁盘空间：
aix系统:/> df -g (linux系统: df -h)
如果需要规范数据库的表空间和路径，还原表空间名称undotbs1和路径不能改变，
可以安装刚才的步骤进行切换回来。
1、创建新的原来的undo表空间
可以在其它的磁盘空间临时创建还原表空间
sql>
create undo tablespace undotbs1
datafile '/u01/app/oracle/undo/undotbs01.dbf'
size 10m autoextend on maxsize 15g;
刚开始为10m，设置自动扩展，最大为15gb
tablespace created.
2、切换undo表空间为新的undo表空间
sql> alter system set undo_tablespace=undotbs1 scope=both;
system altered.
3、验证当前数据库的还原表空间
sql> show parameter undo
name type value
------------------------------------ ----------- --------------
undo_management string auto
undo_retention integer 900
undo_tablespace string undotbs1
4、等待原undo表空间所有undo segment offline
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
segment_name tablespace_name segment_id status
上面对应的undotbs2还原表空间所对应的回滚段均为offline
5、删除undo2表空间
sql> drop tablespace undotbs2 including contents and datafiles;
tablespace dropped.
6、可以再次查看系统磁盘空间：
aix系统:/> df -g (linux系统: df -h)
undo_retention:指定事物commit后undo 将要保存的时间(秒)，在oracle10g中默认的是900秒。
guarantee ： 保证undo_retention参数所设定的时间有效，这个是10g的新功能。
sql> alter tablespace undotbs1 retention guarantee;
sql> alter tablespace undotbs1 retention noguarantee;
在没有guarantee的保证下，oracle并不能保证能够将undo信息存储900秒，如果undo表空间不足，那么oracle将忽略undo_retention的设置，直接覆盖掉以前的undo,这个时候有可能会产生ora-01555错误。如果undo表空间空间足够，那么undo将会保存很长一段时间，直到undo表空间达到maxsize，这个时候才会覆盖undo信息，而且oracle会从最古老的undo信息开始覆盖。
oracle推荐我们将undo 表空间中的datafile 设定maxsize ，不要让它一直自动扩展，如果oracle获得了自动扩展的能力，，那么旧的undo不会被覆盖，到后来undo表空间会越来越大，越来越大，直到将磁盘空间耗尽。
在有guarantee的保证下，oracle将会保证undo信息能够保存到undo_retention设定的值之后才被覆盖，如果这个时候同时执行了很多事物，将undo表空间耗完了，那么那个事物会失败，会报ora-30036 错误，所以使用guarantee一定要慎用，如果非要使用guarantee，那么尽量将undo 表空间设大 一点。
oracle10g开始，如果你设置undo_retention为0，那么oracle启用自动调整以满足最长运行查询的需要。当然如果空间不足，那么oracle满足最大允许的长时间查询，而不再需要用户手工调整。