怎么Shrink Undo表空间,释放过度占用的空间

如何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          011 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          012 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                     .000114441             .000114441          0        14          0 ONLINE                     .000114441             .000114441          0        20          0 ONLINE                     .000114441             .000114441          0        16          0 ONLINE                     .000114441             .000114441          0        17          0 ONLINE                     .000114441             .000114441          0        18          0 ONLINE                     .000114441             .000114441          0        19          0 ONLINE                     .000114441             .000114441          0         0          0 ONLINE                     .000358582             .000358582          011 rows selected.Elapsed: 00:00:00.00

6.删除原UNDO表空间

11:34:00 SQL> drop tablespace undotbs1 including contents;Tablespace dropped.Elapsed: 00:00:03.13

7.检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.

[oracle@danaly ~]$ export ORACLE_SID=+ASM[oracle@danaly ~]$ asmcmdASMCMD> du Used_MB      Mirror_used_MB  21625               21625ASMCMD> exit

空间已经释放。

查看更多 下一篇