UNDO SIZE INFORMATION

set serverout on size 1000000
set feedback off
set heading off
set lines 132
declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "C4"
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';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :'); dbms_output.put_line('====================================================' || chr(10));
for rec1 in get_undo_stat loop
dbms_output.put_line('A) Adjust UNDO tablespace size according to UNDO_RETENTION :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',60,'.')|| ' : ' || TO_CHAR(rec1.c1,'999999') || ' MB');
dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES) ',60,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MB');
dbms_output.put_line(chr(10));
dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',60,'.') || ' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999'))
|| ' MEGS) ',60,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/



===


-- ************* ROLLBACK SEGMENTS ************** --
prompt

clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading 'Tablespace' justify left format a15 truncated
column segment_name heading 'Seg|Name' justify center format a7
column status heading 'Status' justify center format a8
column initial_extent heading 'Initial|(in M)' justify center format 99990.9
column next_extent heading 'Next|(in M)' justify center format 99990.9
column min_extents heading 'Min|Ext' justify center format 99990
column max_extents heading 'Max|Ext' justify center format 999999999990
column pct_increase heading 'Pct|Inc' justify center format 99990
column rbsize heading 'Curr Size|(in M)' justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select
r.tablespace_name,
r.segment_name, r.status,
r.initial_extent/1024/1024 "initial_extent",
r.next_extent/1024/1024 "next_extent",
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 "rbsize"
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;




Seg Initial Next Min Max Pct Curr Size
Tablespace Name Status (in M) (in M) Ext Ext Inc (in M)
--------------- ------- -------- -------- -------- ------ ------------- ------ ---------
UNDOTBS1 _SYSSMU ONLINE 0.1 0.1 2 32765 1
6_24433
81498$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
9_14243
41975$

_SYSSMU ONLINE 0.1 0.1 2 32765 0
10_3550
978943$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
3_20976
77531$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
7_32866
10060$


SYSTEM SYSTEM ONLINE 0.1 0.1 1 32765 0

UNDOTBS1 _SYSSMU ONLINE 0.1 0.1 2 32765 2
4_11520
05954$

_SYSSMU ONLINE 0.1 0.1 2 32765 0
2_22325
71081$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
8_20123
82730$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
5_15274
69038$

_SYSSMU ONLINE 0.1 0.1 2 32765 0
1_37803
97527$