Skip to content

Alter Table Move to Another Tablespace

  • by

Move Table to Another Tablespace

Basically, moving an Oracle table can defragment the data and make it compact so as to gain some benefit when performing queries. Secondly, we move it to another tablespace for making rooms in the crowded tablespace.

1. ALTER TABLE MOVE

Traditionally, we use ALTER TABLE MOVE clause to move a table without any modifier. That is to say, it will move the table in the original tablespace, you should enough space to perform the transition.

SQL> alter table hr.employees move;

Table altered.

SQL> select tablespace_name from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';

TABLESPACE_NAME
------------------------------
EXAMPLE

If an error ORA-14511 may occur when you move the table, it means that the table is a partitioned table, you cannot move it like a normal one. You should use another way to move your partitioned table.

2. ALTER TABLE MOVE TABLESPACE

To move the table to another place and make some rooms for the original tablespace, you can specify tablespace.

SQL> alter table hr.employees move tablespace users;

Table altered.

SQL> select tablespace_name from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';

TABLESPACE_NAME
------------------------------
USERS

The table has been moved from tablespace EXAMPLE to USERS.

3. ALTER TABLE MOVE ONLINE

Starting from release 12.2, we can have ONLINE modifier to let us move tables without blocking transactions like Data Manipulation Language (DML). This feature is just like we did in rebuilding index online.

SQL> alter table hr.employees move tablespace users online;

Table altered.

4. ALTER TABLE MOVE PARALLEL

Additionally, we can move the table parallelly.

SQL> alter table hr.employees move tablespace users online parallel 16;

Table altered.

The best thing is that, the degree of parallelism (DOP) of this table is not affected by this statement.

SQL> column degree format a10;
SQL> select degree from all_tables where owner = 'HR' and table_name = 'EMPLOYEES';

DEGREE
----------
         1

The attribute is not changed.

Please note that, moving an entire table makes all rows move to different data blocks, which changes ROWID of every row. Consequently, all dependent indexes become UNUSABLE. You have to rebuild all denpendent indexes to make them valid again.

Leave a Reply

Your email address will not be published.