oracle9 undo表空间释放,undo表空间使用率过高，且迟迟不释放问题

数据库版本：10.2.0.4 & 10.2.0.5

操作系统版本：HP-UX IA 11.31 & PA 11.23

最近发现undo表空间使用率超过90%，可用率低于10%，我个人用于查看的sql语句如下：

select  round(

(

(select round(sum(bytes) / 1024 / 1024 / 1024, 2)

from dba_undo_extents  where tablespace_name=’UNDOTBS1′ and status=’EXPIRED’ group by  tablespace_name

)

+

nvl((select round(sum(bytes)/1024/1024/1024,2)

from dba_free_space where tablespace_name=’UNDOTBS1′ group by tablespace_name

),0)

)

*100/

(select round(sum(bytes)/1024/1024/1024,2)

from dba_data_files  where tablespace_name=’UNDOTBS1′ group by tablespace_name

),2   )   as "UNDOTBS AVAILABLE %"   from dual

并且在扩展以后，过不了几个小时，使用率又超过了90%，且多个数据库，均出现了这样的情况，虽说通过oracle metalink文档：

Full UNDO Tablespace In 10gR2 [ID 413732.1],oracle遇到undo表空间相关的申请时，会尝试各种方法：

(1).如果当前的extent里有free的block，那么就分配下一个free的block给申请者使用。

(2).否则，如果下一个extent是expired的，那么就对它进行wrap，然后返回第一个block给申请者使用。

这里之所以说是下一个，应该是考虑块或者extent的连续性。

(3).如果下一个区不是expired，那么就从undo表空间中获取空间，看看是否有free的extent，如果有，那么就把它分给undo segment，然后返回新extent的第一个块。

(4).如果undo表空间中，没有free的extent，那么就从处于offline的undo segments中steal处于expired状态的extent来使用，从offline的undo segments中De-allocate处于expired状态的extent，然后把它假如有undo申请需求的undo segment，然后返回extent的第一个块。

(5).如果在offline的undo segments中没有expired的extent可用，那么就从处于online状态的undo segments中steal extent，然后把新的extent添加到当前的提出undo申请需求的undo segment里，然后返回新区的第一个块。

(6). 如果上面的条件还是不满足，那么就extend undo表空间里的数据文件，也就是找undo表空间中autoextend为on的数据文件，然后进行扩展。

如果有能够扩展的数据文件，那么在扩展文件以后，从新的空间了分配extent给申请undo的undo segment，然后返回第一个块。

(7). 如果无法实施第六步，然后将retention降低10%，然后在满足“lower retention value”的基础上，从那些unexpired状态的undo中steal extent。

(8). 如果第七部还是无法实施，那么从任意处于offline状态的undo segments中，steal extent。

(9). 如果还是不行，reuse 自动undo segment中哪些未过期的extent。如果所有的extent的当前都busy(他们都包含未提交的信息)，那么尝试第十步，否则wrap into下一个extent。

(10). 如果还是不行，那么从任意处于online状态的undo segments中，steal extent。

(11). 如果还是不行，那么报错：ORA-30036 unable to extend segment by %s in undo tablespace ‘%s’

上面的是我自己做的翻译，英文原版可以参考：Full UNDO Tablespace In 10gR2 [ID 413732.1]

但是，如果此时数据库一下出现了好几个并发使用undo，且使用量较大的事务，很难保证不报undo表空间不足的错误。

================================================================================================================

新手(包括我自己)看到这里可能会说，这些，你是怎么知道的？没那么神秘，基本就是metalink，牛人之所以牛，基本上是由于如下其中的几点或者全部：

A: 兴趣，人家是真对ORACLE感兴趣，而对大多数人(个人理解)来讲，ORACLE只是谋生的手段，只是一份工作而已。

你会天天做实验吗？人家会。

B：勤奋，认真，且勤于思考、实验

C：有更多的实践机会(由于工作原因)；

D：接触oracle时间比我们长，工作时间比我们久

E：看metalink文档，DSI，官方文档，oracle internal，国内牛人、国外牛人的东西比我们多,且做的实验或者实践比我们多很多(由于工作原因)；

F：认识国内、国外oracle原厂的人，能获得常人获取不到的资源；

G：做过C语言及PLSQL 等的开发，且很精通；

H：懂或者说精通操作系统、中间件等等；

I：了解网络相关的基础知识

J：其它

等接触oracle时间久了，就会发现，其实很多东西并没那么神秘，很多东西，在oracle的metalink都有说明，我们慢慢、慢慢的也就都熟了，都到了。

但是同时，我个人也感到ORACLE真是一个无底洞！

言归正传，解决问题。

=============================================================================================================================

随后，google了一下，也metalink了一下，发现下面的这个比较靠谱：

Automatic Tuning of Undo_retention Causes Space Problems [ID 420525.1]

There are 3 possible alternate workarounds (any one of these should resolve the problem of the alerts triggering unnecessarily):

1.) Set the autoextend and maxsize attribute of each datafile in the undo ts so it is autoextensible and its maxsize is equal to its current size so the undo

tablespace now has the autoextend attribute but does not autoextend:

SQL> alter database datafile ‘’

autoextend on maxsize ;

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the undo tablespace size, instead v$undostat.tuned_undoretention is set to

the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

2.) Set the following hidden parameter in init.ora file:

_smu_debug_mode=33554432

or

SQL> Alter system set "_smu_debug_mode" = 33554432;

With this setting, v$undostat.tuned_undoretention is not calculated based on a percentage of the fixed size undo tablespace, instead v$undostat.tuned_undoretention is

set to the maximum of (maxquerylen secs + 300) undo_retention specified in init.ora file.

3.) Set the following hidden parameter in init.ora:

_undo_autotune = false

or

SQL> Alter system set "_undo_autotune" = false;

With this setting, v$undostat (and therefore v$undostat.tuned_undoretention) is not maintained and the undo_retention used is the one specified in init.ora file.

NOTE:  This means you loose all advantages in having automatic undo management and is not an ideal long term fix.

NOTE: Even with the patch fix installed, the autotuned retention can still grow under certain circumstances.  The fix attempts to throttle back how aggressive that

autotuning will be.  Options 2 and 3 may be needed to get around this aggressive growth in some environments.

原来是：_undo_autotune引起的v$undostat.tuned_undoretention的计算方法问题。

按照上面给出的三种方法，结合我这里的实际情况：

数据库正好使用裸设备存储数据，且在添加数据库文件时，直接将size指定成：裸设备大小-2M，其中的2M是保留给oracle使用，同时关闭数据文件的自动扩展。

符合上面说的第一种情况，同时也觉得第一种方法最保险，风险较小，也容易实施，随后手动做了调整：

先通过select查询生成命令脚本：

select ‘alter database datafile ‘||file_id||’ autoextend on maxsize ‘||bytes/1024/1024||’M;’ from dba_data_files where tablespace_name like ‘UNDO%’;

随后执行脚本：

alter database datafile 1186 autoextend on maxsize 8190M;

alter database datafile 1187 autoextend on maxsize 8190M;

alter database datafile 1188 autoextend on maxsize 8190M;

alter database datafile 1189 autoextend on maxsize 8190M;

alter database datafile 1190 autoextend on maxsize 8190M;

alter database datafile 1191 autoextend on maxsize 8190M;

alter database datafile 1192 autoextend on maxsize 8190M;

alter database datafile 1193 autoextend on maxsize 8190M;

alter database datafile 1194 autoextend on maxsize 8190M;

alter database datafile 1195 autoextend on maxsize 8190M;

调整完成后，过了15分钟后，再次查看，发现约有30%的undo已经转换成expired，转换速度非常快。

所以决定观察几天，如果没有出现大量的ORA-01555错误，那基本就可以放心了。

ORACLE真是个无底洞！