Oracle的自动管理表空间AUM(Auto Undo
Management)经常出现只扩展,不收缩的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性)。如果当UNDO表空间占用很多的磁盘空间时,我们可以采用如下步骤回收UNDO空间:
1. 创建新的UNDO表空间;
语法:create undo tablespace 回滚表空间名称 datafile '文件路径及名称' size XXXm autoextend on next YYYm;
例:如想在C:\oracle\oradata\ora9i下创建一个UNDO表空间UNDOTBS01,表空间的数据文件为UNDOTBS01.DBF,初始大小为200M,并使UNDO表空间为自动扩展,每次扩展100M。
SQL> create undo tablespace UNDOTBS01
2 datafile 'C:\oracle\oradata\ora9i\UNDOTBS01.DBF'
3* size 200m autoextend on next 100m;
2.
切换UNDO表空间为新的UNDO表空间;
语法:alter system set undo_tablespace=新的UNDO表空间名称 scope=both;
例:切换UNDOTBS01为新的表空间。
SQL> alter system set undo_tablespace=UNDOTBS01 scope=both;
3.等待原UNDO表空间所有UNDO SEGMENT OFFLINE,执行如下SQL脚本:
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,
shrinks 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.
注意:红色部分的状态说明原UNDO表空间回滚段没有OFFLINE。 等待一段时间再次执行如上代码看原UNDO空间是否OFFLINE。
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 0
11 rows selected.
这次查询结果表示原UNDO表空间完全OFFLINE,新的UNDO表空间ONLINE,注意USN号码。
4.删除原UNDO表空间(此操作是从数据字典里删除UNDO表空间,为逻辑删除,USDO表空间的数据文件仍在磁盘里。);
语法:drop tablespace 回滚表空间名称including contents;
例:删除原回滚表空间undotbs02
SQL>drop tablespace undotbs02 including contents;
5.用操作系统删除命令删除原有UNDO表空间的数据文件。
至此,UNDO表空间收缩完成。
以上操作为试验成功操作。