Re: moving indexes to new tablespace

Dick Allie wrote:

> Narayanan Olagappan wrote:
> >
> > 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
> >
> > You may try 'alter index <index> rebuild tablespace <new
 tablespace>',
> > if you are working with 7.3.
> Hi,
> set the default tablespace for the user that owns the tables and
> indexes
> to tablespace B before doing the import. Then after the import change
>
> back to the tablespace A.
> Dick

Also, make sure to set quota to 0 on tablespace A before doing the import, set it back to the original value after. Received on Mon Aug 18 1997 - 00:00:00 CDT