How do i give optimal size to my RB segmennts

[puneet]

Hi


1 select n.name, s.optsize
2 from v$rollname n, v$rollstat s
3 where n.usn = s.usn
4* order by n.name
SQL> /

NAME OPTSIZE
------------------------------ ---------
R01
R02
R03
R04
R05
R06
RBIG
SYSTEM

This is what I get and I want to give an optimal size to these segments . Please let me know how do I give that

Regards

[tamilselvan]

Set TIMED_STATISTICS=TRUE, and then run this query.

[marist89]

TIMED_STATISTICS won't help here, you're looking at the optimal size of your rollback segments. The optsize field will show up regardless of whether you have TIMED_STATISTICS on or off.

To set the optimal size of a rollback segment, issue:
alter rollback segment r01 storage (optimal 8M);

[puneet]

How do I find out the current size of my rollback segments !!!

regards

[marist89]

select n.name, s.rssize/1024/1024 rollback_mb, s.optsize, s.extents, s.xacts current_transactions, s.waits, s.gets, s.status, s.extends no_times_extended, s.shrinks no_times_shrunk
from v$rollname n, v$rollstat s
where n.usn = s.usn
order by n.name