Re: Need Help Rebuilding an Index

Joe,

You need to defragment. You don't necessarily need to drop the tablespace. Drop the existing indexes. If there is nothing left in the tablespace, it is now empty. Attempt to create a table almost the size of the tablespace. This will make Oracle try to coalesce the free extents. If it fails, dc_free_extents is probably too small. Kick it up above the number of free extents in the tablespace. Try again to create the table. Drop the table. If it does not coalesce the free space, let me know what it says.

Good luck,

Gary Farmer

>
>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 Wed Jan 03 1996 - 17:51:08 CST