Rem
Rem Header: estimate_undo.sql 31-july-2007
Rem
Rem
Rem    NAME
Rem      estimate_undo.sql
Rem
Rem    DESCRIPTION
Rem      To assist Database Administrators in sizing an UNDO Tablespace for automatic undo management.
Rem      The following script provide the following:
Rem                                                -Current UNDO_RETENTION parameter value
Rem                                                -Estimate the number of Megs needed for the UNDO tablespace with the desired UNDO_RETENTION parameter value.
Rem                                                -Display the current maximum Undo Size
Rem                                                -Display the Undo size and file_name
Rem      Provided by the user by entering the value in &seconds
Rem
Rem    NOTES
Rem      The SQL Statement that Calculates the number of Megs needed for UNDO tablespace is taken from Metalink Note:262066.1
Rem
Rem

set feedback off
set linesize 600
set serveroutput on
set verify off

select value AS "Current UNDO_RETENTION value" from v$parameter where name = 'undo_retention';

prompt
prompt Specify the desired UNDO_RETENTION in seconds. Ex: 86400=24 hours
prompt Using &&seconds for the UNDO_RETENTION.

SELECT round(((UR * (UPS * DBS)) + (DBS * 24)) / 1048576, 0) AS "Megs needed for UNDO"
  FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT (SUM(undoblks) /
                 SUM(((end_time -
                      begin_time) *
                      &Seconds))) AS UPS
           FROM v$undostat), (select block_size as DBS
           from dba_tablespaces
          where tablespace_name =
                (select value
                   from v$parameter
                  where name = 'undo_tablespace'));

select round(sum(bytes) / 1048576, 0) AS "Current Max Undo Size in MB"
  from dba_data_files
 where tablespace_name =
       (select value from v$parameter where name = 'undo_tablespace');

select round(bytes / 1048576,0) AS "UNDO Size in MB" , file_name
from dba_data_files where tablespace_name =
(select value from v$parameter where name = 'undo_tablespace');

set feedback on
set verify on

undefine seconds