Re: Eliminating index files

Sybrand Bakker <gooiditweg_at_nospam.demon.nl> wrote in message news:<qn607vke2jrjm5sl4isfnphms78oiu248q_at_4ax.com>...
> On Thu, 13 Mar 2003 00:42:34 +0100, Rick Denoire
> <100.17706_at_germanynet.de> wrote:
>
> >I need to reorganize a Raid group. One LUN contains index files only,
> >and instead of saving them, I would rather just remove them,
> >reconfigure the LUN adding more harddisks, and then recreate the
> >indexes. Saving and restoring through the network takes several hours.
> >
> >I wonder if the database would still start without the files belonging
> >to the index tablespace. Of course, one could just drop the
> >tablespace, but then I assume that it would not be possible to just
> >rebuild the indexes any more. So how could I free index space without
> >losing their definitions?
> >
> >Thanks
> >Rick Denoire
>
>
> The only other alternatives you have are:
> - moving the datafiles to a different disk
> - running export without exporting the data prior to reorganization.
> This will dump the create index statements which can be extracted with
> imp show=y
>
> Dropping the tablespace is mandatory I am afraid.
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address

Rick, I think the INDEXFILE option is better than show for getting imp to write code for you (less editing required), but it is even better to just use dba_indexes and dba_ind_columns to generate create index scripts; drop the tablespace including contents; do your disk system work; recreate the tablespace; and re-create your indexes. You will also want to generate FK, UK, and PK constraints for the affected objects as breaking the FK constraints speeds the dropping process.

HTH -- Mark D Powell -- Received on Thu Mar 13 2003 - 08:22:18 CST