如何释放过度占用的Shrink Undo表空间

<h1 class="postTitle">
<a id="ctl04_TitleUrl" class="postTitle2" href="http://www.cnblogs.com/rootq/archive/2009/04/18/1438804.html">如何释放过度占用的Shrink Undo表空间</a>
</h1>
<p><strong>OS:Red Hat Enterprise Linux AS release 4 (Nahant)<br>
DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production</strong>
<br>
一台Oracle10gR2数据库报出如下错误:<br><br></p>
<table border="0"><tbody><tr>
<td width="500">
<pre>ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX <br>
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX <br>
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX <br>
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX <br>
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in tablespace SYSAUX</pre>
</td>
</tr></tbody></table>
<br>
登陆检查，发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展<a href="http://www.eygle.com/10g/10g_sysaux_tbs.htm"><span style="color: #0000ff;">SYSAUX</span>
</a>
表空间:<br><table border="0"><tbody><tr>
<td width="500">
<pre>alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m<br>
Tue Nov 29 23:31:38 2005<br>
ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...</pre>
</td>
</tr></tbody></table>
<br>
出现ORA-1237错误，提示空间不足。这时候我才认识到是磁盘空间可能被用完了.<br><br>
是谁"偷偷的"用了那么多空间呢(本来有几十个G的Free磁盘空间的)?<br>
检查数据库表空间占用空间情况:<br><table border="0"><tbody><tr>
<td width="500">
<pre>SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB<br>
2 from dba_data_files group by tablespace_name<br>
3 union all<br>
4 select tablespace_name,sum(bytes)/1024/1024/1024 GB<br>
5 from dba_temp_files group by tablespace_name order by GB;<br><br>
TABLESPACE_NAME GB<br>
------------------------------ ----------<br>
USERS .004882813<br>
UNDOTBS2 .09765625<br>
SYSTEM .478515625<br>
SYSAUX .634765625<br>
WAPCM_TS_VISIT_DETAIL .9765625<br>
HY_DS_DEFAULT 1<br>
MINT_TS_DEFAULT 1<br>
MMS_TS_DATA2 1.375<br>
MMS_IDX_SJH 2<br>
MMS_TS_DEFAULT 2<br>
IVRCN_TS_DATA 2<br><br>
TABLESPACE_NAME GB<br>
------------------------------ ----------<br>
MMS_TS_DATA1 2<br>
CM_TS_DEFAULT 5<br>
TEMP 20.5498047<br>
UNDOTBS1 27.1582031<br><br>
15 rows selected.</pre>
</td>
</tr></tbody></table>
<br>
不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。<br>
显然曾经有大事务占用了大量的UNDO表空间和Temp表空间，Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).<br>
现在我们可以采用如下步骤回收UNDO空间:<br><br>
1.确认文件<br><table border="0"><tbody><tr>
<td width="500">
<pre>SQL> select file_name,bytes/1024/1024 from dba_data_files<br>
2 where tablespace_name like 'UNDOTBS1';<br><br>
FILE_NAME<br>
--------------------------------------------------------------------------------<br>
BYTES/1024/1024<br>
---------------<br>
+ORADG/danaly/datafile/undotbs1.265.600173875<br>
27810</pre>
</td>
</tr></tbody></table>
<br>
2.检查UNDO Segment状态<br><table border="0"><tbody><tr>
<td width="500">
<pre>SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks<br>
2 from v$rollstat order by rssize;<br><br>
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS<br>
---------- ---------- --------------------- ---------------------- ----------<br>
0 0 .000358582 .000358582 0<br>
2 0 .071517944 .071517944 0<br>
3 0 .13722229 .13722229 0<br>
9 0 .236984253 .236984253 0<br>
10 0 .625144958 .625144958 0<br>
5 1 1.22946167 1.22946167 0<br>
8 0 1.27175903 1.27175903 0<br>
4 1 1.27895355 1.27895355 0<br>
7 0 1.56770325 1.56770325 0<br>
1 0 2.02474976 2.02474976 0<br>
6 0 2.9671936 2.9671936 0<br><br>
11 rows selected.</pre>
</td>
</tr></tbody></table>
<br>
3.创建新的UNDO表空间<br><table border="0"><tbody><tr>
<td width="500">
<pre>SQL> create undo tablespace undotbs2;<br><br>
Tablespace created.</pre>
</td>
</tr></tbody></table>
<br>
4.切换UNDO表空间为新的UNDO表空间<br><table border="0"><tbody><tr>
<td width="500">
<pre>SQL> alter system set undo_tablespace=undotbs2 scope=both;<br><br>
System altered.</pre>
</td>
</tr></tbody></table>
<br>
此处使用<a href="http://www.eygle.com/faq/Oracle9i.New.Feature.Spfile.02.htm"><span style="color: #0000ff;">spfile</span>
</a>
需要注意，以前曾经记录过这样一个案例:<a href="http://www.eygle.com/case/spfile.htm"><span style="color: #0000ff;">Oracle诊断案例-Spfile案例一则</span>
</a>
<br>
5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE
<table border="0"><tbody><tr>
<td width="500">
<pre>SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks<br>
2 from v$rollstat order by rssize;<br><br>
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS<br>
---------- ---------- --------------- --------------------- ---------------------- ----------<br>
14 0 ONLINE .000114441 .000114441 0<br>
19 0 ONLINE .000114441 .000114441 0<br>
11 0 ONLINE .000114441 .000114441 0<br>
12 0 ONLINE .000114441 .000114441 0<br>
13 0 ONLINE .000114441 .000114441 0<br>
20 0 ONLINE .000114441 .000114441 0<br>
15 1 ONLINE .000114441 .000114441 0<br>
16 0 ONLINE .000114441 .000114441 0<br>
17 0 ONLINE .000114441 .000114441 0<br>
18 0 ONLINE .000114441 .000114441 0<br>
0 0 ONLINE .000358582 .000358582 0<br><br>
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS<br>
---------- ---------- --------------- --------------------- ---------------------- ----------<br>
6 0 PENDING OFFLINE 2.9671936 2.9671936 0<br><br>
12 rows selected.</pre>
</td>
</tr></tbody></table>
<br>
再看:<br><table border="0"><tbody><tr>
<td width="500">
<pre>11:32:11 SQL> /<br><br>
USN XACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS<br>
---------- ---------- --------------- --------------------- ---------------------- ----------<br>
15 1 ONLINE .000114441 .000114441 0<br>
11 0 ONLINE .000114441 .000114441 0<br>
12 0 ONLINE .000114441 .000114441 0<br>
13 0 ONLINE .000114441 .000114441 0<br>
14 0 ONLINE .000114441 .000114441 0<br>
20 0 ONLINE .000114441 .000114441 0<br>
16 0 ONLINE .000114441 .000114441 0<br>
17 0 ONLINE .000114441 .000114441 0<br>
18 0 ONLINE .000114441 .000114441 0<br>
19 0 ONLINE .000114441 .000114441 0<br>
0 0 ONLINE .000358582 .000358582 0<br><br>
11 rows selected.<br><br>
Elapsed: 00:00:00.00</pre>
</td>
</tr></tbody></table>
<br>
6.删除原UNDO表空间<br><table border="0"><tbody><tr>
<td width="500">
<pre>11:34:00 SQL> drop tablespace undotbs1 including contents;<br><br>
Tablespace dropped.<br><br>
Elapsed: 00:00:03.13</pre>
</td>
</tr></tbody></table>
<p>

7.检查空间情况<br>
由于我使用的ASM管理,可以使用10gR2提供的信工具<a href="http://www.eygle.com/archives/2005/09/oracle_10g_rele.html"><span style="color: #0000ff;">asmcmd</span>
</a>
来察看空间占用情况.</p>
<pre>[oracle@danaly ~]$ export ORACLE_SID=+ASM<br>
[oracle@danaly ~]$ asmcmd<br>
ASMCMD> du <br>
Used_MB Mirror_used_MB<br>
21625 21625<br>
ASMCMD> exit</pre>