SQL> SELECT
((SELECT (NVL(SUM(bytes),0))
FROM dba_undo_extents
WHERE tablespace_name='<UNDO_TABLESPACE_NAME>'
AND status IN ('ACTIVE','UNEXPIRED')) * 100)/
(SELECT SUM(bytes)
FROM dba_data_files
WHERE tablespace_name='<UNDO_TABLESPACE_NAME>')
"PCT_INUSE"
FROM dual;
Cause
The cause of this problem has been identified in:
Bug:5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
It is caused by a wrong calculation of the tuned undo retention value.
Bug:5387030 is fixed in RDBMS 11.1.
Solution
To implement a solution for Bug:5387030, please execute any of the below alternative solutions:
• Upgrade to 11.1 in which Bug:5387030 is fixed
OR
• Apply patchset release 10.2.0.4 or higher in which Bug:5387030 is fixed.
OR
• Download and apply interim Patch:5387030, if available for your platform and RDBMS release. To check for conflicting patches, please use the MOS Patch Planner Tool. If no patch is available, file a Service Request through My Oracle Support for your specific Oracle version and platform.
OR
• Use any of the following workarounds:
1. Set the AUTOEXTEND and MAXSIZE attributes of each datafile of the undo tablespace in such a way that they are autoextensible and the MAXSIZE is equal to the current size (so the undo tablespace now has the AUTOEXTEND attribute but does not autoextend):
SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>
With this setting, V$UNDOSTAT.TUNED_UNDORETENTION is not calculated based on a percentage of the undo tablespace size. Instead it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
2. Set the following instance parameter:
_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 it is set to the maximum of (MAXQUERYLEN secs + 300) and UNDO_RETENTION.
3. Set the following instance parameter:
_undo_autotune = false
With this setting, V$UNDOSTAT (and therefore V$UNDOSTAT.TUNED_UNDORETENTION) is not maintained and the undo retention used is based on the UNDO_RETENTION instance parameter.
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.
References
BUG:5387030 - AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
NOTE:413732.1 - Full UNDO Tablespace In 10gR2 and above
这篇文章本来是用来解决10gR2中的bug，但是在11gR2中同样适用，说明在11gR2中同样存在该bug。在文章提供了3种解决方案：
1).将UNDO表空间对应的数据文件调整为自动扩展，并为其设定一个最大值。
SQL> ALTER DATABASE DATAFILE '<datafile_flename>' AUTOEXTEND ON MAXSIZE <current_size>
客户正是通过这种方式解决了问题，调整之后空间很快得到释放，V$UNDOSTAT.TUNED_UNDORETENTION值立即变小，这和文章前面的解释是完全吻合的，当UNDO表空间对应的数据文件是自动扩展的，那么V$UNDOSTAT.TUNED_UNDORETENTION值的计算就不再依赖于UNDO表空间的百分比（UNDO表空间本身较大）。
2).设置_smu_debug_mode隐藏参数。
_smu_debug_mode=33554432
前面我们已经对这个参数进行了解释，这里再次验证。
3).设置_undo_autotune隐藏参数。
_undo_autotune = false
前面的两种方法没有关闭Oracle的UNDO自动调整RETENTION的功能，将_undo_autotune设置为false，就彻底关闭了自动调整UNDO RETENTION的功能，那么UNDO的RETENTION时间完全依赖于初始化参数UNDO_RETENTION的值，默认值为900秒。
以上三种方法的任意一种方法都可以解决客户面临的该问题。
这篇文章是对我另一篇文章有益的补充：《监控和管理Oracle UNDO表空间的使用》：
我们学习的很多知识点过一段时间都可能忘记，但通过具体的案例我们更容易的记住。
--end--
--------------------------------------------------------------------------------