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.
In this post, we introduce 5 levels of moving table in depth.
- ALTER TABLE MOVE
- ALTER TABLE MOVE TABLESPACE
- ALTER TABLE MOVE ONLINE
- ALTER TABLE MOVE PARALLEL
- ALTER TABLE MOVE UPDATE INDEXES
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.
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.
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 do in rebuilding index online.
SQL> alter table hr.employees move tablespace users online;
Table altered.
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.
ALTER TABLE MOVE UPDATE INDEXES
Moving table makes all rows move to different data blocks, which changes ROWID of every row. Consequently, all dependent indexes become UNUSABLE.
Since release 12.2, to prevent dependent indexes from being UNUSABLE, we can specify UPDATE INDEXES to rebuild them automatically after the table has been moved.
SQL> alter table hr.employees move tablespace users online parallel 16 update indexes;
Table altered.
If you move the table with ONLINE clause, then VALID dependent index will be rebuilt automatically with or without specifying UPDATE INDEXES.
For 12.1 and earlier releases, you have to manually rebuild all dependent indexes to make them valid again.