Undo Status

Undo Status:
prompt
prompt #########
prompt UNDO Size
prompt #########
set head off
select to_char(sum(a.bytes)/1024/1024,'999,999')||' mb' undo_size
  from v$datafile a,
       v$tablespace b,
       dba_tablespaces c
 where c.contents = 'UNDO'
   and c.status = 'ONLINE'
   and b.name = c.tablespace_name
   and a.ts# = b.ts#
/
set head on

column block_size heading 'Block Size' new_value block_size
select to_number(value) block_size
  from v$parameter
 where name = 'db_block_size'
/

set verify off
prompt
prompt ################
prompt UNDO UTILIZATION
prompt ################
col tablespace_name heading 'Undo Tablespace' format a15
col status heading 'Status' format a15
col mb heading 'Size MB' format 999,999
select tablespace_name,
       status,
       round(sum(blocks) * &block_size/1024/1024,2) MB
  from dba_undo_extents
  group by tablespace_name,
           status
  order by tablespace_name,
           status
/

col undo_retention heading 'undo_retention' format a30
select to_char(value,'99,999')||' seconds or '||to_char(value/60,'99')||' minutes' undo_retention from v$parameter where name = 'undo_retention'
/

col undo_tablespace heading 'undo_tablespace' format a30
select value undo_tablespace from v$parameter where name = 'undo_tablespace'
/

col undo_management heading 'undo_management' format a30
select value undo_management from v$parameter where name = 'undo_management'
/

 
Notes:
In Undo Segments there are three types of extents,
Unexpired – Undo data whose age is less than the undo retention time.
Expired – Undo data whose age is greater than the undo retention time.
Active – Undo data that is part of an active transaction.

The sequence for using UNDO extents:

1. A new extent will be allocated from undo when the requirement arises. As undo is written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) wraps into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.

2. If this fails because there are no available free extents and we cannot autoextend the datafile, then Oracle attempts to steal an expired extent from another undo segment.

3. If that fails then it tries to reuse an unexpired extent from the current undo segment.

4. If that fails, then it tries to steal an unexpired extent from another undo segment.

5. If all else fails, an Out-Of-Space error will be reported.