Re: Question on tablespace/file allocation

Marcia Thomasson wrote:
>
> > >
> > > I have gone through the excercise of sizing each table and allocating
> the
> > > appropriate amount of space for it when it is created, and I have also
> > added
> > > up the total amount of space I need for each tablespace and created the
> > > tablespace with that amount of space in two files (with the amount
> divided
> > > by 2). I have also done this for my indexes.
> >
> > Sizing the tables is a good exercise, but what do you mean by 'allocating
> > the appropriate amount of space'. If you just mean make sure the
> tablespace
> > is bigger than the total size of the objects to be stored in it then that
> is
> > good. If however you mean create the table with an initial extent equal to
> > the expected size of the data then that is bad. You should be using
> uniform
> > extent sizes within a tablespace.
> >
>
> Why is it bad to create the table with an initial extent equal to the
> expected size of the data? The potential sizes of my tables could vary
> widely within a tablespace, why would I want to allocate the same extent
> sizes for all of them? Wouldn't that lead to either a) way too much space
> being allocated for some tables (if I err'ed on the "large" side) or b)
> tables being fragmented (if I err'ed on the "small" side)?

Try think about it this way...If the entire tablespace is a jigsaw puzzle (that the database has to try fill), then its a lot easier if all the pieces are the same size. When you drop table, then 'n' mb is freed up, and every new object is also 'n' mb, then you can guarantee that it will fit. If you have an assortment of different sizes then its difficult to know when a tablespace is "full" (ie there might be lots of free space but none of it of the correct size)

The "keep everything in one extent" policy is a myth. You may require many tablespaces, but try subscribe to the rule of only one extent size per tablespace (or even better use locally managed tablespaces with uniform allocation)

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."