如何Shrink Undo表空间,释放过度占用的空间
环境:
os:red hat enterprise linux as release 4 (nahant)
db:oracle database 10g enterprise edition release 10.2.0.1.0 - production
一台oracle10gr2数据库报出如下错误:
ora-1653: unable to extend table sysman.mgmt_system_error_log by 8 in tablespace sysaux
ora-1653: unable to extend table sysman.mgmt_system_error_log by 8 in tablespace sysaux
ora-1653: unable to extend table sysman.mgmt_system_error_log by 8 in tablespace sysaux
ora-1653: unable to extend table sysman.mgmt_system_error_log by 8 in tablespace sysaux
ora-1653: unable to extend table sysman.mgmt_system_error_log by 8 in tablespace sysaux
登陆检查，发现是sysaux表空间空间用尽,不能扩展,尝试手工扩展sysaux表空间:
alter database datafile '+oradg/danaly/datafile/sysaux.266.600173881' resize 800m
tue nov 29 23:31:38 2005
ora-1237 signalled during: alter database datafile '+oradg/danaly/datafile/sysaux.266.600173881' resize 800m...
出现ora-1237错误，提示空间不足。这时候我才认识到是磁盘空间可能被用完了.
是谁"偷偷的"用了那么多空间呢(本来有几十个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 ;
（经测试，在9i环境下后面还要加上datafile '/opt/..../undotbs2.dbf' size 1024m）
tablespace created.
4.切换undo表空间为新的undo表空间
sql> alter system set undo_tablespace=undotbs2 scope=both;
system altered.
创建了新的undo表空间以后，如果不知道系统使用的是pfile还是spfile文件，应使用参数both,会同时修改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;
（本人经测试觉得其实是否可以用drop tablespace undotbs1 including contents and datafiles;直接连硬盘里面的dbf文件件一起删除）
tablespace dropped.
elapsed: 00:00:03.13
7.检查空间情况
由于我使用的asm管理,可以使用10gr2提供的信工具来察看空间占用情况.
[oracle@danaly ~]$ export oracle_sid=+asm
[oracle@danaly ~]$ asmcmd
asmcmd> du
used_mb mirror_used_mb
21625 21625
asmcmd> exit
空间已经释放。