解决undo 表空间过大导致磁盘空间不足的问题

解决undo 表空间过大导致磁盘空间不足的问题

undo表空间不断扩大问题的原因：1有较大的事务量让oracle undo 自动扩展，产生过度占有磁盘空间的情况。2有较大事务没有收缩或者没有提交所导致。

1.查看还原表空间所在磁盘是否使用率过高，及linux 系统哪个磁盘处于比较空闲的状态

：$df -lh

2.在oracle 数据库中查看所有表空间的占用率：

SQL> SELECT Total.name "Tablespace Name",
2 nvl(Free_space, 0) Free_space,
3 nvl(total_space-Free_space, 0) Used_space,
4 total_space
5 FROM
6 (select tablespace_name, sum(bytes/1024/1024) Free_Space
7 from sys.dba_free_space
8 group by tablespace_name
9 ) Free,
10 (select b.name, sum(bytes/1024/1024) TOTAL_SPACE
11 from sys.v_$datafile a, sys.v_$tablespace B
12 where a.ts# = b.ts#
13 group by b.name
14 ) Total
15 WHERE Free.Tablespace_name(+) = Total.name
16 ORDER BY Total.name
17 /

星期二 5月 29 第 1
Space Usage for Database in Meg

EXAMPLE 21.25 78.75 100
STAGING 1.9375 1.0625 3
SYSAUX 35.4375 524.5625 560
SYSTEM 3.125 686.875 690
UNDOTBS1 194.375 85.625 280
USERS .9375 4.0625 5
SQL>
SQL> tti off
SQL>

查询undo表空间的路径

sql>select file_name ,bytes/1024/1024
from dba_data_files
where tablespace_name like 'UNDOTBS1';

3.检查还原表空间的segment的状态的信息：

sql>select usn,xacts,rssize/1024/1024/1024,
hwmsize/1024/10244/1024, shrinks
from v$rollstat order by rssize;

0 0 .000358582 .000035844 0
1 0 .002067566 .000206676 0
5 0 .002067566 .000206676 0
6 0 .002067566 .000206676 0
10 0 .002067566 .000206676 0
4 0 .002067566 .000206676 0
9 0 .003105164 .000310395 0
8 0 .004020691 .000401912 0
7 0 .005973816 .000597148 0
3 0 .022575378 .002256656 0
2 0 .036552429 .003653816 0

这表示该还原表中还存在11个回滚对象，及数据库的历史数据。

4. 创建新的还原表空间。
sql>create undo tablespace undotbs2
datafile 'F:\ORACLE11GR2\ORADATA\ORCL\UNDOTBS02.DBF'
size 10m autoextend on maxsize 1GB;

5.在oracle数据库中把默认的还原表空间切换成新建的还原表空间undotbs2
sql>alter system set undo_tablespace=undotbs2 scope=both ;

6.验证当前数据库使用的还原表空间是否为新建的还原表空间：
sql>show parameter undo

7.等待旧的还原表空间所有的数据变成脱机状态（undo segment offline）:
sql>select t.segment_name , t.tablespace_name, t.segment_id, t.status
from dba_rollback_segs t;

SYSTEM SYSTEM 0 ONLINE
_SYSSMU10_3176102001$ UNDOTBS1 10 ONLINE
_SYSSMU9_1126410412$ UNDOTBS1 9 ONLINE
_SYSSMU8_1557854099$ UNDOTBS1 8 ONLINE
_SYSSMU7_137577888$ UNDOTBS1 7 ONLINE
_SYSSMU6_1834113595$ UNDOTBS1 6 ONLINE
_SYSSMU5_1018230376$ UNDOTBS1 5 ONLINE
_SYSSMU4_2369290268$ UNDOTBS1 4 ONLINE
_SYSSMU3_991555123$ UNDOTBS1 3 ONLINE
_SYSSMU2_2082490410$ UNDOTBS1 2 ONLINE
_SYSSMU1_1518548437$ UNDOTBS1 1 ONLINE

上面对应的回滚段均为 offline 时即可干掉 之前的undo表空间了
sql>drop tablespace undotbs1 including contents and datafiles;