采用如下步骤回收UNDO空间:
1.确认文件
2.检查UNDO Segment状态
3.创建新的UNDO表空间
4.切换UNDO表空间为新的UNDO表空间
5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE
再看:
6.删除原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 .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. 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来察看空间占用情况.
空间已经释放。
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM [oracle@danaly ~]$ asmcmd ASMCMD> du Used_MB Mirror_used_MB 21625 21625 ASMCMD> exit
空间已经释放。