Re: drop index or export table

Hi,

Steven <SCHEN_at_prodigy.net> wrote in article <80es3p$3ep0$1_at_newssvr04-int.news.prodigy.com>...
> I have a table with 200M and 8 extents, but its 2 indexes are 250M with
over
> 30 extents each.
>
> Do we drop and rebuild indexes or export/import table?
Drop and recreate indexes. Make sure that you have 275m extra space in your SYSTEM's temporary tablespace where the index is temporarily built by SYS account before it is moved to the specified tablespace.

> Would table
> export/import also rearrange index extent?

I believe that is correct. For the following to work you must have exported with compress=y.

If you want to see what those storage parameters are then use: imp indexfile=create_script.sql

This will not import any data, it simply creates a script for indexes and tables (commented out).

While you are at it, you may want to try

imp show=y log=create_script.sql

With the same effect, but creates script for almost every object including packages, functions,triggers,
constraints,grants etc.

> On the other hand, some indexes are associated with constraint columns.
> Haven't tried if they can be dropped.
I don't believe that matters.

Suresh Bhat
Oracleguru
www.oraclegur.net Received on Thu Nov 11 1999 - 13:35:04 CST