====== Oracle Undo management ======

** > 10g with Tunded undo management - compute size of undo : **
<sxh sql>
SELECT (UR * (UPS * DBS))/1024/1024 AS "Bytes" FROM (select max(tuned_undoretention) AS UR from v$undostat), (SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), (SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
</sxh>

**Active transactions with undo and rollback segments :**
<sxh sql>
col o format a10
col u format a10
select osuser o, username u, sid,
segment_name s, substr(sa.sql_text,1,200) txt
from v$session s,
v$transaction t,
dba_rollback_segs r,
v$sqlarea sa
where s.taddr=t.addr
and t.xidusn=r.segment_id(+)
and s.sql_address=sa.address(+)
And substr(sa.sql_text,1,200) is not null
order by 3;

col name format a8
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select username, osuser,
t.start_time, r.name, t.used_ublk "ROLLB BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/
</sxh>

**Tivoli monitoring query for Undo utilization :**
<sxh sql>
set LINESIZE 132;
col "NAME" format A80
col "Free %" format 999
col "Size [MB]" format 999999999
col "Used [MB]" format 999999999
select 'SPACE:' || a.tablespace_name "NAME", 
sum(a."MB") "Size [MB]", 
sum(nvl(b."MB", 0)) "Used [MB]" ,
(sum(a."MB") - sum(nvl(b."MB", 0))) / sum(a."MB")  * 100 "Free %" 
from 
(select tablespace_name, sum(bytes)/1024/1024 "MB" from dba_data_files df
where exists (select 1 from dba_undo_extents where tablespace_name = df.tablespace_name)
group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 "MB" from dba_undo_extents
where status = 'ACTIVE' group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
group by a.tablespace_name
order by "Free %" desc;
</sxh>

**Recommended UNDO size :**
<sxh sql>
set linesize 300
col "UNDO RETENTION [Sec]" for a30
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'
/
</sxh>