Re: Importing tables to a different tablespace?

It sort of can be done. First off indexes should not be in the same tablespace with tables.

The easiest way to move indexes it to use the rebuild index command with the unrecoverable command. With this command you can specify the new storage parameters.

As for the table.
Option 1. You can export it then drop the tablespace that you do not want it in. You will lose anything else in that tablespace. Then when you do the import it will put the table in the default tablespace of the user. This of course has some obvious drawbacks.

Option 2. Export table. Use schema manager, Toad or some other utility to create a create table script. Edit the script to change the storage parameters. Drop table. use your script to recreate the table where you want it. Then use import to bring the data back in. There is a option that will ignore the error that is thrown when the table is found.

Option 3. One of my programmers has had some sucess opening the dumpfile in a text editor and changing the tablespace parameters. It was a pain though.

Option 4. Always pick a good spot for tables before you start putting stuff in them. Also make sure they are sized properly and do not throw to many extents. Received on Thu Jan 27 2000 - 20:19:13 CST