My Oracle Support Banner

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

Last updated on SEPTEMBER 20, 2023

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 8.1.7 to 11.2]
Oracle Database - Enterprise Edition - Version to [Release 11.2]
Oracle E-Business Suite Technology Stack - Version 12.1.3 to 12.1.3 [Release 12.1]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


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

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)


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.