Re: Rollback segment/tablespace growth problem

"Mr. Chow Wing Siu" <wschow_at_Comp.HKBU.Edu.HK> wrote in message news:9p5l2j$h2c$1_at_net44p.hkbu.edu.hk...
> Lawrence Simela <lsimela_at_mahalini.prestel.co.uk> wrote:
> > Issue
> > ALTER ROLLBACK SEGMENT rollback_seg_name SHRINK TO new_size;
> > If you do not specify TO new_sized then it will be shrunk to OPTIMAL
size or
> > MINEXTENTS if OPTIMAL was not specified in storage clause
> > hth
> > lawrence
>
> Hi,
>
> I did it before seeing your post. It works. My next questions
> are: how to determine the optimal size, why rbs cannot shrink
> by itself and need to be done manually, if optimal size is set,
> what's the sql "alter rollback segment r01 shrink"?
>
> Why designer2000 needs higher extension and causes rbs full recently?
>
> Thanks.
>
> --
> Johnson Chow

1 a guideline for optimal is setting it to 2 times minextents times the size of an extent
2 if you set optimal Oracle *will* shrink the rollback segment automatically.
You may not want that because continuous space allocation is -before 8i- very expensive.
3 alter rollback segment <segment_name> shrink is the sql to shrink manually.
4 Designer 2000 is a complicated system and many tables and indices are typically involved in one single transaction. 150M is definitely not big for a rollback segment.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Sat Sep 29 2001 - 02:35:31 CDT