Oracle undo size and shrinking Oracle Tips by Burleson Consulting

Question:  How do I size my UNDO to make sure that all of my Oracle tasks complete successfully?  What are the guidelines for sizing the undo_retention and the UNDO tablespace?

Answer:  Undo sizing is straightforward.  The larger your undo tablespace, the more "before images" you can hold for long-running DML (updates, inserts).  The Oracle UNDO tablespace must have enough space to rollback any long-running update.

Given enough time,  Oracle will automatically manage the UNDO usage.

In Oracle, UNDO size can be controlled with the undo_retention parameter and the size of the UNDO tablespace, and the setting for these are determined by the level of DML activity in the database:

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 (
SEE CODE DEPOT FOR FULL SCRIPTS
       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'
/

Also, note that the automatic undo management feature introduced in Oracle 9i has been improved with the following capabilities.

Some of the undo management related initialization parameters are eliminated in Oracle Database 10g as they are automatically calculated. They include max_rollback_segment, row_locking, undo_suppress_errors, serializable, and transaction_auditing.

This script is useful in determining a HWM for sessions, for UNDO sizing:

rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
SEE CODE DEPOT FOR FULL SCRIPTS
   v$session b,
   v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;