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.
