简介Oracle回滚段空间回收步骤

简介Oracle回滚段空间回收步骤

不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。

　　是谁"偷偷的"用了那么多空间呢(本来有几十个G的Free磁盘空间的)?

　　检查数据库表空间占用空间情况:

　　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 GB
　　5 from dba_temp_files group by tablespace_name order by GB;
　　TABLESPACE_NAME GB
　　------------------------------ ----------
　　USERS .004882813
　　UNDOTBS2 .09765625
　　SYSTEM .478515625
　　SYSAUX .634765625
　　WAPCM_TS_VISIT_DETAIL .9765625
　　HY_DS_DEFAULT 1
　　MINT_TS_DEFAULT 1
　　MMS_TS_DATA2 1.375
　　MMS_IDX_SJH 2
　　MMS_TS_DEFAULT 2
　　IVRCN_TS_DATA 2
　　TABLESPACE_NAME GB
　　------------------------------ ----------
　　MMS_TS_DATA1 2
　　CM_TS_DEFAULT 5
　　TEMP 20.5498047
　　UNDOTBS1 27.1582031
　　15 rows selected.

　　不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。

　　显然曾经有大事务占用了大量的UNDO表空间和Temp表空间，Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(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.

　　此处使用spfile需要注意，以前曾经记录过这样一个案例:Oracle诊断案例-Spfile案例一则

　　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来察看空间占用情况.

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

　　空间已经释放。