Re: moving indexes to new tablespace

fuocor_at_novachem.com (Richard Fuoco) wrote:

>hi, i am trying to move indexes from tablespace A to tablespace B
>I do an export of the schema that I want to reorg and move the indexes
>I drop the tablespace with contents
>I recreate the tablespace A and create a new tablepsace B
>I run imp with the indexfile option to create a sql file with all the
>create index commands.
>I modify this file and replace the tablespace A with B
>I import the dmp file with the indexes=no option
>I run the script to create the indexes in new tablespace B
>
>The problem is that any indexes that are created from a primary key
>constraint still goes into tablespace A. The dump file contains alter
>table commands to create the primary constraint
>
>Is there a way to move these primary key constraints (indexes) to
>tablespace B without writing all the scripts to drop and recreate. Or
>does anyone have a sql statement to build the scripts from the data
>dictionary.
>
>Any help would be appreciated
>
>Regards
>Richard Fuoco
>fuocor_at_novachem.com

I have used ALTER INDEX ... REBUILD TABLESPACE ... to move unique indexes, it may work for primary keys.

--
Mark Wagoner
mwagoner_at_no.spam.medplus.com

To reply, remove no.spam from my mail address.