Re: drop index or export table

Thank you for the points. Yes, it is using temp tablespace instead of system tablespace.

The foreign keys are pains. They prevent you to rebuild. I had the error saying 'cannot extent temp segent in index tablespace' on the foreign key index rebuilding. I don't know if it is related to foreign key issue (I did not disable it then)

Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in message news:01bf2d28$9888b0e0$a504fa80_at_mndnet...
> Correction:
>
> My previoous post should say SYS's temporary tablespace not SYSTEM's.
>
> Also, if the indexed constraints are foreign keys then you may have to
> disable them before recreating.
>
> Further, Conner suggested rebuilding, this will require twice as much disk
> space as recreating,
> because the old index is still present until the new one is finished. So
> you will need 600M which is a lot.
> Except having the old index for quesries, there is no great advantage in
> rebuilding index if you are not moving the index to another tablespace.
>
>
> Suresh
>
>
> Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in article
> <01bf2c8b$ae5efc00$a504fa80_at_mndnet>...
> > 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 Sat Nov 13 1999 - 21:12:12 CST