| In this Document
APPLIES TO: 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';
COUNT(STATUS)
-------------
21
Space available reported by dba_free_space:
SUM(BYTES)/(1024*1024) TABLESPACE_NAME
---------------------- ---------------------
3 UNDOTBS1
58.4375 SYSAUX
3 USERS3
4.3125 SYSTEM
103.9375 USERS04
When the transactions run:
SUM(BYTES)/(1024*1024) TABLESPACE_NAME
---------------------- ----------------
58.25 SYSAUX
98 USERS3
4.3125 SYSTEM
87.9375 USERS04 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 | 
