如何Shrink Undo表空间,释放过度占用的空间
采用如下步骤回收UNDO空间:

1.确认文件

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

FILE_NAME
--------------------------------------------
BYTES/1024/1024
---------------
+ORADG/danaly/datafile/undotbs1.265.600173875
          27810

2.检查UNDO Segment状态

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

       USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------------- ---------------------- ----------
         0          0            .000358582             .000358582          0
         2          0            .071517944             .071517944          0
         3          0             .13722229              .13722229          0
         9          0            .236984253             .236984253          0
        10          0            .625144958             .625144958          0
         5          1            1.22946167             1.22946167          0
         8          0            1.27175903             1.27175903          0
         4          1            1.27895355             1.27895355          0
         7          0            1.56770325             1.56770325          0
         1          0            2.02474976             2.02474976          0
         6          0             2.9671936              2.9671936          0

11 rows selected.

3.创建新的UNDO表空间

SQL> create undo tablespace undotbs2;

Tablespace created.

4.切换UNDO表空间为新的UNDO表空间

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
  2 from v$rollstat order by rssize;


       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
        14          0 ONLINE                     .000114441             .000114441          0
        19          0 ONLINE                     .000114441             .000114441          0
        11          0 ONLINE                     .000114441             .000114441          0
        12          0 ONLINE                     .000114441             .000114441          0
        13          0 ONLINE                     .000114441             .000114441          0
        20          0 ONLINE                     .000114441             .000114441          0
        15          1 ONLINE                     .000114441             .000114441          0
        16          0 ONLINE                     .000114441             .000114441          0
        17          0 ONLINE                     .000114441             .000114441          0
        18          0 ONLINE                     .000114441             .000114441          0
         0          0 ONLINE                     .000358582             .000358582          0

       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
         6          0 PENDING OFFLINE             2.9671936              2.9671936          0

12 rows selected.

再看:

11:32:11 SQL> /

       USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
        15          1 ONLINE                     .000114441             .000114441          0
        11          0 ONLINE                     .000114441             .000114441          0
        12          0 ONLINE                     .000114441             .000114441          0
        13          0 ONLINE