Re: Help: Moving tables and indexes from one tablespace to another
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 indexcreated 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