分类： Oracle
2009-04-08 09:57:46
OS:win2003 Enterprise 64bit
Oracle version: 64bit for win
早上看邮件报某库硬盘快要满了，对比数据文件的size,发现undo比原来大了20G左右。
曾经有大事务占用了大量的UNDO表空间，并且不能收缩。所有，只能新建一个UNDO，并
把原来的UNDO删除来释放空间。
1，确认现在的UNDO
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 600
undo_tablespace string UNDOTBS1
2，检查UNDO Segment状态
SQL>select usn,xacts,rssize/1024/1024,hwmsize/1024/1024,STATUS
from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024 HWMSIZE/1024/1024 STATUS
---------- ---------- ---------------- ----------------- ------------
269 0 .1796875 7.7421875 ONLINE
267 1 .3046875 .8671875 ONLINE
0 0 .3671875 .3671875 ONLINE
283 0 .3671875 .8671875 ONLINE
282 0 .3671875 .9921875 ONLINE
270 0 .4296875 .6171875 ONLINE
274 0 .4296875 2.8671875 ONLINE
281 0 .4296875 .8046875 ONLINE
272 1 .4296875 7.4921875 ONLINE
279 0 .4921875 7.7421875 ONLINE
268 0 .4921875 1.1796875 ONLINE
USN XACTS RSSIZE/1024/1024 HWMSIZE/1024/1024 STATUS
---------- ---------- ---------------- ----------------- ------------
271 0 .4921875 .6796875 ONLINE
275 0 .4921875 7.6171875 ONLINE
280 0 .5546875 1.5546875 ONLINE
276 0 .5546875 7.8046875 ONLINE
273 0 .6171875 1.4921875 ONLINE
284 0 .6171875 1.0546875 ONLINE
278 0 .6796875 .9296875 ONLINE
277 0 .6796875 2.1171875 ONLINE
3.创建新的UNDO表空间
SQL>create undo tablespace UNDOTBS2
datafile 'M:\undotbs2_1.dbf' size 10M autoextend on maxsize 10000M;
4.切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;
5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE
SQL>select usn,xacts,rssize/1024/1024,hwmsize/1024/1024,STATUS
from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024 HWMSIZE/1024/1024 STATUS
---------- ---------- ---------------- ----------------- ---------------
269 0 .1796875 7.7421875 ONLINE
267 1 .3046875 .8671875 ONLINE
0 0 .3671875 .3671875 ONLINE
283 0 .3671875 .8671875 ONLINE
282 0 .3671875 .9921875 ONLINE
270 0 .4296875 .6171875 ONLINE
274 0 .4296875 2.8671875 ONLINE
281 0 .4296875 .8046875 ONLINE
272 1 .4296875 7.4921875 ONLINE
279 0 .4921875 7.7421875 ONLINE
268 0 .4921875 1.1796875 ONLINE
USN XACTS RSSIZE/1024/1024 HWMSIZE/1024/1024 STATUS
---------- ---------- ---------------- ----------------- ---------------
271 1 .4921875 .6796875 ONLINE
275 0 .4921875 7.6171875 ONLINE
280 0 .5546875 1.5546875 ONLINE
276 1 .5546875 7.8046875 ONLINE
273 0 .6171875 1.4921875 ONLINE
284 0 .6171875 1.0546875 ONLINE
278 0 .6796875 .9296875 ONLINE
277 0 .6796875 2.1171875 ONLINE
11 0 79.9921875 267.171875 PENDING OFFLINE
6.删除原UNDO表空间
SQL> drop tablespace undotbs1 including contents;
Remark:
windows中执行了rop tablespace undotbs1 including contents;但此命令并没有从系统中并没有
把数据文件删除，就算在操作系统中有删除命令也不能成功。所以用能过下面方法将文件缩小达到
释放空间的目的。
create undo tablespace UNDOTBS1 datafile 'K:\UNDOTBS01.DBF' size 10M reuse;
drop tablespace undotbs1 including contents;
下次重启就能把UNDOTBS01.DBF删除了。
