RBS Defragmentation

[bdipankar]

Hi all

I need your help to solve the following problem.
My RBS is too much fragmented.

1.Tell me the sign of excessive fragmentation of RBS ?

2. How do I defragment my RBS ?

[JDoyle]

You do not state how you've determined that your RBS (I assume this means tablespace) is "much too fragmented". Probably the simplest thing to do is to drop and recreate rollback segments.

[JDoyle]

Some further points on the issue of rollback segments.
1.) rollback segments should be stored in a rollback tablespace, in which nothing but RBS are stored.
2.) for a given rollback segment, all extent sizes should be the same...i.e. initial and next should be equal.
3.) rollback segments dynamically extend to handle a large transaction, and they will keep the allocated space unless the parameter optimal is set for that rollback segment. RBS can get very stretched out and needlessly take up a lot of space unless you set optimal. To check for this parameter:

select a.name, b.optsize from v$rollname a, v$rollstat b where a.usn=b.usn;

If this parameter is set, then your rollback segments will automatically shrink to this size after overextending themselves. Using optimal does not necessarily, however, relieve you of the burden of properly sizing rollback segments.
4.) if you have a very large transaction, assign it to a properly sized, large rollback segment.
5.) you can shrink a stretched out rollback segment.
alter rollback segment RB4 shrink to 1M; (for example)
6.) monitor shrinks and wraps using UTLBSTAT/UTLESTAT to get data helpful for proper sizing.

hope this helps.