SNAP SHOT 01555

REFERENCES FROM http://taliphakanozturken.wordpress.com/tag/undo-tablespace/

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
 g.undo_block_per_sec) / (1024*1024) 
 "NEEDED UNDO SIZE [MByte]"
 FROM (
 SELECT SUM(a.bytes) 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#
 ) d,
 v$parameter e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
 WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
1500
UNDO RETENTION [Sec] 
--------------------
900
NEEDED UNDO SIZE [MByte]
------------------------
1753.582031249999999999999999999999999998

The previous query may return a “NEEDED UNDO SIZE” that is less than the “ACTUAL UNDO SIZE”. If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

Check the current status of UNDO

SQL> select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;

TABLESPACE_NAME
--------------------------------------------------------------------------------
STATUS                              GB
--------------------------- ----------
UNDOTBS1
UNEXPIRED                   5.43377686

UNDOTBS1

EXPIRED                     18.8825684

To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace

SQL> select     tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*8/(1024*1024) total_space
from     dba_undo_extents
group by    tablespace_name, status;
  2    3    4    5    6    7
TABLESPACE_NAME
--------------------------------------------------------------------------------
STATUS                      Extent Count Total Blocks TOTAL_SPACE
--------------------------- ------------ ------------ -----------
UNDOTBS1
ACTIVE                                 1         1024    .0078125

UNDOTBS1
UNEXPIRED                            911       712216  5.43377686

UNDOTBS1
EXPIRED                             4218      2474976  18.8825684

To list all Undo datafiles with status and size

show parameter undo

show parameter db_block_size

col tablespace_name form a20

col file_name form a60

set lines 120

select tablespace_name, file_name, status, bytes/1024/1024 from dba_data_files

where tablespace_name=(select tablespace_name from dba_tablespaces where contents='UNDO');