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

Randy,
vhills_at_utdallas.edu (Randy H) wrote:
>Hi,
>
>I currently have a tablespace made up of several raw partions (extents) that
>contains both tables and indexes. This is heavily used. My plan is to create
>two new tablespaces (made up of one raw partition each) and then move all the
>indexes to one tablespace and all the tables to the second tablespace.
>
>What is the best way to do this? Should I export just the tables and then
>just the indexes (is this possible). I want to keep the original tablespace
>intact so users can access it until it is time to switch them over to the new
>setup.
>
>Thanks in advance for any help you can give me on this.
>
>- Randy Hills

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. 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,

Hope this helps ]
R2
rmramos.gbs_at_worldnet.att.net
Independent Oracle Consultant Received on Fri Aug 16 1996 - 00:00:00 CEST