Re: Rollback segments and unlimited extents?

"Rob Williamson" <robw_at_physics.umd.edu> wrote in message news:3BD58F47.6C991156_at_physics.umd.edu...
> Could anyone comment on my recent TAR to oracle which is concerning
> whether
> the 4096 maxextents makes any sense at all? Either I am not getting
> some basic
> fact or I am correct in my assumption that 4096 is synonomous with
> unlimited in
> some way and was done on purpose for the reasons below?
>
> ORACLE:
> If you are looking for the a way to calculate MAXEXTENTS for the default
> storage clause of the tablespace, then set it to UNLIMITED and not worry
> about it at all. There is no performance penalty in allowing an object
> to grow to a very large number of extents.
>
> If you are looking for validation of the formula used, then the formula
> is has some problems.
> 1) It assumes that the file size provided will be in M, and the INITIAL
> will always be provided in K.
> 2) I am assuming that no rounding will take place when doing the
> division.
> 3) It ignores the fact that there is some amount of overhead that is
> required in the datafile header used for bookkeeping.
>
> Also, If the storage clause if always specified for any objects created
> in the tablespace, then the default storage clause on the tablespace is
> never used.
>
> MY RESPONSE:
> New info : Thanks for that I will use unlimited for all my tablespaces
> except:
> What about Rollback Segments
> the docs specifically recomend not using unlimited.
> Again I submit your NOte: 117424.1 How to create an 8i DB in Unix
> page 4 has
> create tablespace RBS DAtafile 'Filename' SIZE 12M
> reuse
> MINIMUM EXTENT 128K
> DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 2
> MAXEXTENTS 4096 PCTINCREASE 0);
> Rounding or other overhead aside I don't see how
> 4096 * 128 * 1024 < 12M ( my figure is 536.87 M )
> Now I realize we probably won't use 4096 extents ( I believe we
> can never without adding data files ) but that is no reason to use
> the number if it is the same as unlimited. I would think of using
> maxextents < 90 ( probably 80 with your overhead comment )? Is the
> reason so
> you can add datafiles and not have to modify maxextents?
> Does Oracle recomend using the storage clause with the create rollback
> segment
> command rather than using the default storage clause? I have yet to see
> a
> script that actually does that including NOTE:117424.1?
>
> Thanks
> Rob

Unlimited for Oracle means 2 to the power of 32 -1 so the maxvalue for an ordinary 32 bit number.
Definitely much higher than 4096

Regards
Sybrand Bakker
Senior Oracle DBA Received on Tue Oct 23 2001 - 15:04:33 CDT