Re: Help: Moving tables and indexes from one tablespace to another

From: Randy <rmramos.gbs_at_worldnet.att.net>
Date: 1996/08/17
Message-ID: <4v3a93$bvu_at_mtinsc01-mgt.ops.worldnet.att.net>#1/1


rhills_at_hitachi-hisus.com (Randy H) wrote:
>In article <4v0va6$ick_at_mtinsc01-mgt.ops.worldnet.att.net>,
> Randy Ramos <rmramos.gbs_at_postoffice.worldnet.att.net> wrote:
>
>>There are a couple of ways to do this. As far as a recommendation I
>>always lean towards Export/Import. I do this simply because you may have
>>constraints or triggers on the table and these do not get copied when you
>>do a CREATE TABLE AS SELECT. Before you Export the table change the
>>Quota limit on the existing tablespace for the user to ZERO. When you
>>Import them have them go to a differnet efault tablespace. <snip>
>>Hope this helps ]
>>R2
>>rmramos.gbs_at_worldnet.att.net
>>Independent Oracle Consultant
>>
>
>Thanks Randy -- I admire you, you take the time to answer many questions on
>this usenet group. This is to be commended.
>
>Please clarify : You said:
>-->As far as just moving indexes go. You can disable the Primary Key, If the
>-->index was created as a constraint then Enable the constraint specifying the
>-->storage clause for the index which obviously you would indicate as a
>-->different tablespace than the data segment ...
>
>Please expound on the disable enable. If you could give an example of the
>statement I would really appreciate it.
>
>Thanks,
>
>Randy.

I'm so confused is Randy the sender or receiver. Just kidding here's the syntax.

ALTER TABLE tablename ENABLE
PKorFK USING INDEX TABLESPACE tablespacename;

where: tablename = table

          PKorFK = The Primary or Foreign Key Constraint
          tablespacename = The name of the tablespace you want the index 
created into.

The assumption here is you have the necessary privs and tablespace quotas.

Hope this Helps
R2 Received on Sat Aug 17 1996 - 00:00:00 CEST

Original text of this message