Re: Moving Indexes to a different tablespace

From: Frank Kobylanski <this_person_at_thisplace.com>
Date: 1997/03/06
Message-ID: <5fmg5g$sqp$1@newsin-1.starnet.net>#1/1

vgoel_at_emi.net (Vikram Goel) wrote:

>Bhasker,

>The steps to move the indexes to their own tablespace can be achived
>easily. First drop your primary keys, then create explicit indexes
>(name them) for your primary keys using the 'use index' clause, within
Rather than dropping the primary keys (which requires having to recreate them (knowing all the columns, column order, etc.), we disable them. This automatically deletes the underlying index.

alter table <table_name> disable primary key

We then enable the index with storage defintion.

alter table <table_name> enable primary key using index <storage stuff as usual, including new tablespace)

As normal, any foreign keys pointing to the primary key you are working with would need to be disabled first and then enabled after you have finished with the primary key.

Frank

>this clause use the tablespace sub clause to specify the tablespace
>name. The same method for unique indexes. To determine the current
>index names, query the dba_constraints view.

>Hope this helps.
>
Received on Thu Mar 06 1997 - 00:00:00 CST