Re: Need Help Rebuilding an Index

You wrote:
Looks like you seem to be using default extent allocation parameters assigned during the tablespace creation for all of the tables and indexes. This may not be the best way to create tables and indexes, especially when you already know the data volume of each of the objects. Try to specify the initial extent size close to the maximum requirement of each of the objects you create (the size is limited by the amount of contiguous space you can find on the tablespace to allocate). The next extent size can be adjusted according to the availability of contiguous chunks on your tablespace afterwards. I am afraid there's no fixed formula to arrive at a number, but we can certainly make an intelligent guess in advance, to avoid the king of problem you mentioned. However, your situation doesn't seem something to be alarmed. Just pick up the table or index on which extent allocation has failed, and adjust its next extent size by 'alter object storage (whatever..)' to make it use the then available contiguous space on the tablespace.
Feel free to write if you have further questions.

Goo luck, and happy new year!

Umesh K. Tiwari
>
>Hi.
>
>I need to rebuild an index on a big table and don't have enough
adjacent
>free space in that tablespace to do it.
>
>This tablespace (LGIDX) contains only large (>50MB) indexes. Right
now, it
>contains only 5 objects. These objects are also indexes.
>The tablespace would be big enough to hold these indexes *plus* the
one I'm
>attempting to rebuild *if* it was not fragmented.
>
>Here's what I would like to do:
>
>1. Make a cold backup
>2. Create a script to re-build the indexes that already exist in
> that tablespace.
>3. Drop the tablespace using "alter tablespace LGIDX offline drop"
>4. Re-create the tablespace using (same size, on same disk drive):
>
> SQLDBA> create tablespace LGIDX
> datafile '/u15/oradata/plgidx001.dbf' size 250M,
> '/u15/oradata/plgidx002.dbf' size 250M
> default storage (
> initial 10M next 10M
> minextents 1 maxextents 99
> pctincrease 0)
> online;
>5. Re-create the indexes in the newly de-fragmented tablespace
using the
> script I created in #2.
>6. Make another cold backup.
>
>Will this work? Or should I just add another datafile to the LGIDX
>tablespace and skip all this extra work?
>
>Thanks!
>
>Joe Johnson
>Highland Community College
>
Received on Sun Dec 31 1995 - 14:03:34 CST