How to Move Tables from One Tablespace to Another. (Doc ID 147356.1)

Last updated on SEPTEMBER 20, 2023

Applies to:

Goal

This is intended for any DBA who wishes to move tables across tablespace.

NOTE:
This article covers also information as was published in previously available Note 158162.1

There are 2 methods we can use to do this. One is to use a combination of
"alter table X move tablespace Y" and "alter index X rebuild tablespace Y"
-- this works in Oracle8i release 8.1 and up ONLY.
The other method is an Export/Import

When deciding which method is best for your situation, please consider the following:

- the alter table move/alter index rebuild is more flexible and faster than the
exp/imp (and less error prone -- you never actually drop the objects).
Additionally, it would be easy to modify the script to move TABLES to one
tablespace and INDEXES to a different tablespace. The drawback to using
this method is that you cannot move a table with a LONG or LONG RAW. You
must exp that table and imp it into a table. You can do this easily by
exporting the table with the LONG/LONG RAW, dropping that table
-- creating an empty version of this table in the new tablespace and
importing just that table.

- if you use the exp/imp, it is up to you to ensure that no modifications
happen to the tables after you begin the export.
There are no mechanisms in place to ensure this -- you must do this (else
you will lose changes)

Solution