Undo Lifetime

Undo information has different states during it’s lifecycle, depending on running transactions and retention settings.

There are three states or types of extents in the Undo tablespace: ACTIVE, EXPIRED and UNEXPIRED. Oracle is still using Rollback segments, but with Automatic Undo Management these are completely controlled by Oracle.

ACTIVE

Active undo extents are used by transactions and will always be active, because they are needed for Rollback. The UNDO_RETENTION setting is not used here, because one can not say something like: ‘after 900 seconds you are not allowed to rollback anymore…’

You will get ‘ORA-30036 unable to extend segment in Undo tablespace‘ errors when no more space is left to store ACTIVE Undo. This will automatically rollback the transaction causing it. TheNOSPACEERRCNT column in V$UNDOSTAT is a good indication how many times this has occurred.

EXPIRED

Expired extents are not used by transactions, the data in these extends is committed and the UNDO_RETENTION time has passed, so it is not needed for Read Consistency.

UNEXPIRED

Unexpired extents are non-active extents that still honour UNDO_RETENTION. The transactions belonging to these undo extents are committed, but the retention time has not passed: You still want/need these for Read Consistency!

When the Undo mechanism requires more extents for ACTIVE extents, it is allowed to steal UNEXPIRED extents when there are no EXPIRED extents left for reuse and it can not allocate more free extents (autoextend maxsize reached or fixed tablespace size). One can check the steal-count inUNXPSTEALCNT in V$UNDOSTAT.

You will get ‘ORA-01555 snapshot too old‘ errors if no Read Consistency information for a query is available. The SSOLDERRCNT in V$UNDOSTAT will show a count of these errors.