How To Move Table To Another Tablespace

Onur Çınar

6 years ago

In this article, I will show you how to move a table to another tablespace. Actually it is very simple to move table with just one command.

ALTER TABLE test_table MOVE TABLESPACE new_tablespace;

1	ALTER TABLE test_table MOVE TABLESPACE new_tablespace;

However, you must be careful on indexes and lobs. All the indexes on the table will be invalidated because of table moving. So you must rebuild all indexes on the table.

ALTER INDEX index_name_on_test_table REBUILD;
--or
ALTER INDEX index_name_on_test_table REBUILD TABLESPACE new_tablespace;--if you want to change index tablespace

ALTER INDEX index_name_on_test_table REBUILD;

--or

ALTER INDEX index_name_on_test_table REBUILD TABLESPACE new_tablespace;--if you want to change index tablespace

If your table has lob columns, you must move them, too.

ALTER TABLE test_table MOVE LOB (lob_column) STORE AS (TABLESPACE new_tablespace);

1	ALTER TABLE test_table MOVE LOB (lob_column) STORE AS (TABLESPACE new_tablespace);