Re: segments/extents ? maxextents?

Answers embedded

On Sun, 14 Oct 2001 16:38:27 -0400, Rob Williamson <robw_at_physics.umd.edu> wrote:

>Two questions that relate to Tablespace and Table creation:
>
>The manuals say not to create an rollback segment tablespace with
>unlimited
>maxextents. So what do I choose and why? What about initaial and next?
>For a small database <500M I was imagining a 10M datafile with
>default storage (initial 256K next 256K minextents 2 maxextents 4096
> pctincrease 0 )
>Does this mean that my RB segments are 256K
>or each extent is 256K or both?

each extent is 256k, the initial size of the rollback segment is 2(minextents) * 256k = 512K. The maxsize is 4096 * 256k

>Do you figure out the max size of a rollback segment by initial x
>maxextents ?

Provided the size of the initial extent equals the size of the next extent, which you should always do for rollback segments

>In which case 4096 would be larger than 10M
>so then I could figure out maxextents by filesize / initial ( as long as
>next is the same
>and pctincrease = 0 )? Is this wrong?

No
>This calcualtion would yield 10M/256K= 37.9 extents or 37 depending on
>how the
>block size internal rounding would work.
>

37 extents

>This question actually led me to question #2 which is when you
>create a table what is the size of the segment?
The size of the segment is the size of the initial extent, provided you didn't set minextents

>I have heard that you should try ( but not too hard to get your tables
>or objects (
>especially static tables) into one segment which if what I said above is
>true

This is a MYTH.
Of course you should try to get *small* tables (1M or less) in 1 extent, for other tables there is no reason at all
>for a 1M static table I would want the storage clause for the table to
>be
>storage clause ( initial 1M next 256k pctincrease 0 minextents 1
>maxextents 4 )
>This would allow the table to double in size up to 2M?
>

You should *always* go for equally sized initial and next in order to avoid fragmentation.

>Am I way off with this stuff the Oracle 8i DBA handbook is good but only
>if you really understand the guts of oracle which I am attempting to do.
>
>I guess to summarize:
>Is intial the size of your segment?
>Does does initial + ( maxextent * next ) < datafile size make sense
>And how do you decide on the RBS initial & max
maxextent * (either initial or next)
>
>Does DB_Block_Size get figured into any of this?
The size of an extent should always be a multiple of db_block_size

>
>Thanks for any help in advance.
>
>Rob

Sybrand Bakker, Senior Oracle DBA Received on Sun Oct 14 2001 - 16:17:56 CDT