解决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    (selecttablespace_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  WHEREFree.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
    fromdba_data_files
    wheretablespace_name like 'UNDOTBS1';

3.检查还原表空间的segment的状态的信息：
    
  sql>selectusn,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 tablespaceundotbs2
     datafile 'F:\ORACLE11GR2\ORADATA\ORCL\UNDOTBS02.DBF'
     size 10m autoextend on maxsize 1GB;
    
5.在oracle数据库中把默认的还原表空间切换成新建的还原表空间undotbs2
sql>alter system set undo_tablespace=undotbs2scope=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 undotbs1including contents and datafiles;