Move tables/indexes to another tablespace

I lost your original message, but this can possibly help you.

We had the same problem with transferring a table from one to another tablespace.
Here is the solution we choose.

The owner of the tables should perform these steps !

1. Backup your system.
2. Owner of destination tablespace may not have 'unlimited tablespace' privilege. (Otherwise quota settings are not effective)
3. Export table,indexes and grants from the table you want.
4. Drop the table in Oracle.
5. Alter the source tablespace to QUOTA = 0
6. Make the destination tablespace the default tablespace.
7. Import table. This table will be put in the default tablespace, with is the one you want.

If you want your table and indexes in different tablespaces :

7. Import table with INDEX=N and GRANT=N. The table + data will be imported in the default tablespace.
8. Make the tablespace you want for the indexes, the default tablespace.
 Import table with ROWS=N and INDEX=Y and GRANT=Y. Only indexes will be made in the default tablespace.

9. Restore the old tablespace values as you want them.

We are working with Oracle 7.1.4.1.40 on a IBM 3090 mainframe. I assume that on other platforms there will be slight differences.

I hope this will hlep you.

Hein v. Vroonhoven
(software engineer Akzo Nobel Information Services Holland) Received on Fri Mar 07 1997 - 00:00:00 CET