Re: How to determine the initial and next extent size of a table/ index?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 21 Mar 2004 07:38:42 -0800
Message-ID: <2687bb95.0403210738.5051cffb@posting.google.com>

herman.lam_at_eds.com (Herman) wrote in message news:<e08e1f56.0403210356.514f6196_at_posting.google.com>...
> "DJ" <nospamplease_at_goaway.com> wrote in message news:<R7Y6c.33$Aq3.26_at_newsfe3-win.server.ntli.net>...
> > "Herman" <herman.lam_at_eds.com> wrote in message
> > news:e08e1f56.0403200542.69a3ba65_at_posting.google.com...
> > > Dear Oracle Experts,
> > >
> > > If I just needed to recreate an existing table and its index with new
> > > storage attributes, what should I base on in determining the initial
> > > and next extent size of the table and its index?
> > >
> > > Besides, will there be any problem (e.g. dependency problem) for the
> > > following two methods to recreate a table/ index?
> > >
> > > 1. export the data, truncate the table, alter the table with a new
> > > storage attributes, import the data
> > >
> > > 2. create another table (with new storage attributes) with subquery to
> > > copy the existing table, drop the old table, rename the new table
> > >
> > > Thanks
> > >
> > > Herman
> >
> > use LMT's then you dont have to worry about storage clauses
> >
> > also you cannto alter the initial extent of a table so first method wont
> > work really
>
> Thanks

Herman, how you size tables depends on your tablespace management policies. As pointed out the use of locally managed tablespaces using either uniform extents or auto-allocate both simplify the DBA's life. But if you have an existing Dictionary managed tablespace and recreation of the entire tablespace as locally managed is not a short term option then I think you consider the follwing:

1- how large is the object and how fast is it growing
2- how fragmented is the tablespace where the table or index is stored
3- how many extents do you have to work with (max no you not want the

object to not exceed for various management reasons)

After considering the above pick the initial and next sizes that best fit what you have to work with.

The Concepts and DBA manuals provide good information on tablespace management. After a review you should consider developing a plan to migrate to the use of LMT, if resources allow.

HTH -- Mark D Powell -- Received on Sun Mar 21 2004 - 09:38:42 CST