Moving objects between tablespaces

At some point, you may need to move data between tablespaces.

Getting ready

First, create your tablespaces. Once the old and new tablespaces exist, we can issue the commands to move the objects inside them.

How to do it…

Tablespaces can contain both permanent and temporary objects.

Permanent data objects include tables, indexes, and TOAST objects. We don't need to worry too much about TOAST objects because they are created in and always live in the same tablespace as their main table. So, if you alter the tablespace of a table, its TOAST objects will also move:

ALTER TABLE mytable SET TABLESPACE new_tablespace;

Indexes can exist in separate tablespaces, and moving a table leaves the indexes where they are. Don't forget to run ALTER INDEX commands as well, one for each index, as follows:

ALTER INDEX mytable_val_idx SET TABLESPACE new_tablespace;

Temporary objects cannot be explicitly moved...