Re: Alter RBS Errors;

Pretty much... they're about the only things you can or would want to change about a rollback segment.

Optimal is the size that a rollback segment will attempt to shrink to whenever a new transaction wishes to place its rollback in a new extent within the segment (ie, the shrink only ever happens when transactions are causing the rollback segment's head pointer to move from on extent to another, an event known as a 'wrap'). You cannot prevent a rollback segment from ballooning in size: it takes just one user to start a transaction and then fail to commit it (or roll it back). Therefore, potentially, what you wanted to be (say) 4Mb, could suddenly grow to be 4Gb in size; the automatic shrink induced by optimal is designed to deal with that sort of thing, and get the segment back down to something more reasonable in size.

However: when does the shrink actually take place? When somebody's transaction is causing the segment to wrap from one extent to another. In other words, right in the *middle* of someone's transaction. And all transactional activity grinds to a halt as the segment works out which extents are no longer needed and can be disposed of. Therefore, automatic shrinking of rollback segments impacts upon performance (and, as a further unpleasant side effect, increases your chances of receiving an ORA-1555 by an order of magnitude). So I always advise never to set it. Let the segments balloon in size: who cares? It's only disk space, after all. Buy some more hard disks if space is that tight.

Then at the dead of night when no-one gives a damn, you can cause an 'alter rollback segment blah shrink to 4m' command to fire off. In other words, *you* control when the shrink happens, not Oracle. And you arrange it to take place when no transactional or reporting activity can be affected.

Regards
HJR

--
-----------------------------------------------
Resources for Oracle : http://www.hjrdba.com
===============================

"Md Irfan" <irfan_pk_at_hotmail.com> wrote in message
news:c42168e7.0204080018.67fc6b2f_at_posting.google.com...


> Thanks Howard for your beautiful explaination.

> So then what is the use of ALTER ROLLBACK SEGMENT , does it applies

> only to MAXEXTENTS or OPTIMAL? What is the importance of Optimal.

>

> Thanks.

> Irfan