Re: moving indexes to new tablespace

From: Mary Travis <Mary.B.Travis_at_bridge.bellsouth.com>
Date: 1997/08/14
Message-ID: <5svkdi$pa2@atglab10.atglab.bls.com>#1/1

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

Richard,

If there are no foreign key constraints dependent on the primary keys with indexes that you want to move out of tablespace A you can... ALTER TABLE schema.table DISABLE CONSTRAINT constraint_name; COMMIT;
ALTER TABLE schema.table ENABLE CONSTRAINT constraint_name

	USING INDEX
	STORAGE (storage clause here if wanted)
	TABLESPACE B;

This works because the way Oracle actually disables primary and unique key constraints is to drop the index that enforces the constraint. Oracle rebuilds the index when the constraint is enabled.

If there are foreign key constraints dependent on the primary keys you want to disable then you will have to identify and disable those foreign keys first. The information on which foreign keys are dependent on which primary keys is all in the dba_constraints or user_constraints catalog views.

Hope this helps,
Mary

Mary Travis
BellSouth Telecommunications
email: Mary.B.Travis_at_bridge.bellsouth.com Received on Thu Aug 14 1997 - 00:00:00 CDT