How to Shink the Undo Space

在shrink undo表空间之前。
一般可以先通过联合查询dba_data_files和dba_temp_files来联合查询，当前表空间数据文件的大小
SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB
  2  from dba_data_files group by tablespace_name
  3  union all
  4  select tablespace_name,sum(bytes)/1024/1024/1024 GM
  5  from dba_temp_files group by tablespace_name order by GB;

TABLESPACE_NAME                        GB
------------------------------ ----------
ICMLNF32                        .09765625
ICMLSTXT                        .29296875
SYSTEM                          .29296875
ICMVFQ04                        .29296875
ICMSFQ04                          .390625
USERS                          .784545898
TEMP                             0.9765625
INDX                           1.21795654
ICMLSNDX                       1.54296875
UNDO01                         2.78320313
ICMLFQ32                       3.46679688

11 rows selected.

我们看到undo01有2G的空间，我们可以通过shink来释放过度消耗的undo空间。
首先我们来看一下undo file的状态：
SQL> select file_name,bytes/1024/1024/1024 from dba_data_files
  2  where tablespace_name like 'UNDO%';

FILE_NAME
----------------------------------------------------------------
BYTES/1024/1024/1024
--------------------
/opt/oracle/oradata/icmnlsdb/undo/icmnlsdb_undo_01.dbf
          2.78320313

之后我们可以检查一下rollback segment的状态（其中usn是回滚段的号）
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat
or  2  der by rssize;

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------------- ---------------------- ----------
         0          0            .000396729             .000396729          0
         9          0            .044063568             .044063568          0
         1          0            .046016693             .046016693          0
         5          0            .046993256             .046993256          0
         6          0            .048946381             .048946381          0
         7          0            .048946381             .048946381          0
         8          0            .050899506             .050899506          0
         2          0            .051876068             .051876068          0
         3          0            .051876068             .051876068          0
         4          0            .054805756             .054805756          0
        10          0            .055782318             .055782318          0

11 rows selected.

之后创建一个新的undo tablespace
SQL> create undo tablespace undotbs2
  2  datafile /opt/oracle/oradata/icmnlsdb/undo/undotbs2.dbf' size 10M;

tablespace created

切换undo表空间为新的undo表空间
SQL> alter system set undo_tablespace=undotbs2

System altered.

之后，系统会offline所有属于UNDO01的回滚段信息。我们可以利用之前的查询回滚段的SQL来再次查询

全部自动offline后，我们就可以删除我们之前的undo tablespace了
drop tablespace undo01 including contents;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12361284/viewspace-999/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-999/