Oracle: How to size UNDO Tablespace for Automatic Management

Here are some queries to figure out how to manage your UNDO Tablespace from 10g onwards. Oracle strongly recommends using UNDO_MANAGEMENT=AUTO.

If your UNDO tablespace is fixed size, some of the queries may help you. All of them are from Oracle Metalink.Replace UNDOTBS1 with name of your UNDO tablespace.

--UNDO Tablespace size
Select SUM(BYTES)/1024/1024 as "MB"
from dba_data_files
where tablespace_name = 'UNDOTBS1';

--Free space available
Select SUM(BYTES)/1024/1024 as "MB"
from dba_FREE_SPACE
where tablespace_name = 'UNDOTBS1';

--Active/Expired segments
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 as "MB", COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

--To calculate space required for your UNDO to grow
SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1024/1024 AS "MB"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) 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'));

UR=UNDO_RETENTION (in seconds)
UPS=Number of UNDO data blocks generated per second
DBS=DB_BLOCK_SIZE (in bytes)

--SQL to find the active transaction that is consuming the UNDO
-- Replace UNDOTBS02 with your UNDO tablespace name

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.machine,
--s.osuser,s.terminal,s.module,s.schemaname,
s.state,s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 Undo_Size_KB,
t.status Transaction_Status,t.start_time,
t1.tablespace_name,sq.sql_text
FROM SYS.v_$rollname r, SYS.v_$session s, SYS.v_$transaction t, SYS.v_$parameter x, dba_rollback_segs t1, sys.v$sql sq
WHERE s.taddr = t.addr
and s.sql_id = sq.sql_id(+)
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS02'