Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2] Information in this document applies to any platform. Checked for relevance on 30-Jun-2010 Checked for relevance on 31-Jan-2012
SYMPTOMS
In a 10gR2 database with an UNDO tablespace created in NO AUTOEXTEND mode, having transactions running in the database, apparently the UNDO tablespace appears to be full.
Also many UNEXPIRED undo segments can be seen when selecting from dba_undo_extents view, although no ORA-1555 or ORA-30036 errors are reported.
CHANGES
Starting with 10gR2, the max retention was introduced.
CAUSE
Here is a small test case for investigating this behavior: Before starting any transaction in the database:
SQL> select count(status) from dba_undo_extents where status = 'UNEXPIRED';
COUNT(STATUS) ------------- 463
SQL> select count(status) from dba_undo_extents where status = 'EXPIRED';
COUNT(STATUS) ------------- 20
SQL> select count(status) from dba_undo_extents where status = 'ACTIVE';
The Undo Block allocation algorithm in Automatic Undo Management is the following :
1. If the current extent has more free blocks then the next free block is allocated.
2. Otherwise, if the next extent expired then wrap in the next extent and return the first block.
3. If the next extent is not expired then get space from the UNDO tablespace. If a free extent is available then allocate it to the undo segment and return the first block in the new extent.
4. If there is no free extent available, then steal expired extents from offline undo segments. De-allocate the expired extent from the offline undo segment and add it to the undo segment. Return the first free block of the extent.
5. If no expired extents are available in offline undo segments, then steal from online undo segments and add the new extents to the current undo segment. Return the first free block of the extent.
6. Extend the file in the UNDO tablespace. If the file can be extended then add an extent to the current undo segment and then return the block.
7. Tune down retention in decrements of 10% and steal extents that were unexpired, but now expired with respect to the lower retention value.
8. Steal unexpired extents from any offline undo segments.
9. Try to reuse unexpired extents from own undo segment. If all extents are currently busy (they contains uncommitted information) go to the step 10. Otherwise, wrap into the next extent.
10. Try to steal unexpired extents from any online undo segment. 11. If all the above fails then return ORA-30036 unable to extend segment by %s in undo tablespace '%s'
When the UNDO tablespace is created with NO AUTOEXTEND, following the allocation algorithm, here is the explanation for this correct behavior:
For a fixed size UNDO tablespace (NO AUTOEXTEND), starting with 10.2, we provide max retention given the fixed undo space, which is set to a value based on the UNDO tablespace size. This means that even if the undo_retention is set to a number of seconds (900 default), the fixed UNDO tablespace supports a bigger undo_retention time interval (e.g: 36 hours), based on the tablespace size, thing that makes the undo extents to be UNEXPIRED. But this doesn't indicate that there are no available undo extents when a transaction will be run in the database, as the UNEXPIRED undo segments will be reused.
SOLUTION
This is a correct behavior, concerning an UNDO tablespace created with AUTOEXTEND OFF in 10gR2, so there is not need to add more space to it or be concerned by the fact that it appears to be 100% full.
NOTE: If you do experience ORA-1555 or ORA-30036 errors and see these same symptoms, please refer to Note 420525.1
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2016-08-31 ORACLE参数max_shared_servers空值与零的区别