《oracle回滚段空间回收步骤》由会员分享，可在线阅读，更多相关《oracle回滚段空间回收步骤（5页珍藏版）》请在金锄头文库上搜索。
1、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
2、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_NAM
3、E 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表空间大小,然后取消其自动扩展属性).现在我们可以采用如下步骤回收
4、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
5、 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
6、 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 datafile E:ORACLEORADATAORA_DBUNDOTBS02.DBF size 100m; Tablespace created.（数据文件UNDOTBS02.DBF，随表空间undotbs2一起创建） 4.切换UNDO表空间为新的UNDO表空间SQL a
7、lter system set undo_tablespace=undotbs2 scope=both; System altered. 此处使用spfile需要注意，以前曾经记录过这样一个案例:Oracle诊断案例-Spfile案例一则5.等待原UNDO表空间所有UNDO SEGMENT OFFLINESQL 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/10
8、24/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
9、 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
10、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 .00011444
11、1 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来察看空间占用情况.oracledanaly $ export ORACLE_SID=+ASM oracledanaly $ asmcmd ASMCMD du Used_MB Mirror_used_MB 21625 21625 ASMCMD exit 空间已经释放。
