Re: Rollback Segment Trouble

In article <36e0d0b6.6002768_at_news.chaven.com>,   amerar_at_unsu.com (Arthur Merar) wrote:
>
> Hello,
>
> I have a couple of questions revolving around rollback segments. Any
> help would be appreciated.
>
> First, on Thursday I received a call that users were getting messages
> of follback segments not being able to get any more extents. So, I
> first extended all the rollback segments by 15 extents. That did not
> help, so I extended the tablespace by 15, and that did not help
> either. So, finally I shrunk all the rollback segments to optimal and
> that worked.
>
> I used Desktop DBA to do some reverse engineering on the rollback
> segment to see how it was built. Interesting.....although the Optimal
> parameter was specified, there was no value. And, the Optimal column
> in v$rollstat is NULL. Why is this?
>
> What is a good size for the optimal on a rollback segment? These are
> short queries running against this database. Here is the code that
> made all the rollback segments in my database:
>
> CREATE ROLLBACK SEGMENT RB01
> TABLESPACE RBS
> STORAGE (INITIAL 262144
> NEXT 262144
> MINEXTENTS 10
> MAXEXTENTS 500
> OPTIMAL )
>
> What should optimal be?
>
> Also, what should my normal response be when a rollback segment runs
> out of room like that?
>
> Thanks for your help.
>
> Arthur
> amerar_at_unsu.com
>
>

Mr. Higgins posted a pretty good reply, but he never posted an answer to what to set your optimal setting to. Why not start with the smallest possible size which is your minextents (10) times your extent size (256k) and then monitor the extends, shrinks and aveshrink from v$rollstat.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Mar 08 1999 - 08:02:39 CST