Re: moving indexes from tablespace A to B

fuocor_at_novachem.com wrote in article <863126384.29067_at_dejanews.com>...
> The problem is that the primary constraint indexes still are in
> tablespace A. The dump contains the alter table command with the primary
> key option. Does anyone know how I can move these indexes to the new
> tablespace B without writing tons of scripts. Or anyone have a script to
> create the scripts from data dictionary

First run the script produced by:

select 'alter table '||table_name||' disable constraint '||constraint_name||';'

   from user_constraints
   where constraint_type='P';

Then run:

select 'alter table '||table_name||' enable constraint '||constraint_name||' using index tablespace NEW_TS;'

   from user_constraints
   where constraint_type='P';

   You will still need to use exp/imp with indexfile=whatever to move those indexes not bound to constraints.

-- 
Doug Harris
Sr. Database Administrator
Linktek Corporation