Move Partition to Another Tablespace
A partitioned table is one type of table which data is divided into several segments, where "segment" is essentially different from "schema object". Simply put, a segment contains concrete data.
Different segments can store in different tablespaces, that's why you cannot move a partitioned table like a normal one. If you did treat it like a normal table, you receive error ORA-14511.
There're 2 major steps to move a partitioned table to another tablespace completely.
Move All Partition Segments
Although we planned to move all partitions to another tablespace, the statements in this post are also useful for defragmenting one single partition to make it compact.
In this step, we have to move every single partition to another tablespace in order, but first of all, we have to compose statements for moving individual partition.
There 4 variations to move partitions to another tablespace.
- ALTER TABLE MOVE PARTITION
- ALTER TABLE MOVE PARTITION ONLINE
- ALTER TABLE MOVE PARTITION PARALLEL
- ALTER TABLE MOVE PARTITION UPDATE INDEXES
ALTER TABLE MOVE PARTITION
In this case, we'd like to move a partitioned table SH.SALES to another tablespace.
Compose Executable SQL Statements
To move them individually, we have to compose moving statement for them.
SQL> column stmts format a120;
SQL> set linesize 255 pagesize 1000;
SQL> select 'alter table ' || table_owner || '.' || table_name || ' move partition ' || partition_name || ' tablespace USERS;' stmts from all_tab_partitions where table_owner = 'SH' and table_name = 'SALES' order by 1;
STMTS
--------------------------------------------------------------------------------
alter table SH.SALES move partition SALES_1995 tablespace USERS;
alter table SH.SALES move partition SALES_1996 tablespace USERS;
alter table SH.SALES move partition SALES_H1_1997 tablespace USERS;
...
Execute SQL Statements
Then we execute the above statement one by one.
SQL> alter table SH.SALES move partition SALES_1995 tablespace USERS;
Table altered.
SQL> alter table SH.SALES move partition SALES_1996 tablespace USERS;
Table altered.
SQL> alter table SH.SALES move partition SALES_H1_1997 tablespace USERS;
Table altered.
...
Please note that, moving any partition makes dependent indexes unusable, you have to manually rebuild those indexes or add UPDATE INDEXES clause.
ALTER TABLE MOVE PARTITION ONLINE
Starting from release 12.2, we can add ONLINE modifier to ALTER TABLE MOVE PARTITION to move tables without blocking transactions like Data Manipulation Language (DML). This feature is just like we did in rebuilding index online.
SQL> select 'alter table ' || table_owner || '.' || table_name || ' move partition ' || partition_name || ' tablespace USERS online;' stmts from all_tab_partitions where table_owner = 'SH' and table_name = 'SALES' order by 1;
You can take the advantage to move partitions online.
ALTER TABLE MOVE PARTITION PARALLEL
To make them execute parallelly, you may add PARALLEL clauses to them.
SQL> select 'alter table ' || table_owner || '.' || table_name || ' move partition ' || partition_name || ' tablespace USERS online parallel 8;' stmts from all_tab_partitions where table_owner = 'SH' and table_name = 'SALES' order by 1;
The degree of parallelism can be adjusted to meet your needs.
ALTER TABLE MOVE PARTITION UPDATE INDEXES
Moving partitions make dependent indexes UNUSABLE if you didn't specify ONLINE clause.
SQL> column index_name format a20;
SQL> column partition_name format a20;
SQL> select index_name, partition_name, status from dba_ind_partitions where index_owner = 'SH' and index_name like 'SALES%' and status <> 'USABLE' order by 1,2;
INDEX_NAME PARTITION_NAME STATUS
-------------------- -------------------- --------
SALES_CHANNEL_BIX SALES_Q1_1998 UNUSABLE
SALES_CHANNEL_BIX SALES_Q1_1999 UNUSABLE
SALES_CHANNEL_BIX SALES_Q1_2000 UNUSABLE
...
To overcome such problem, we can specify UPDATE INDEXES to rebuild them automatically after partitions have been moved.
In this case, it's unnecessary to add UPDATE INDEXES to every moving statement, we add it to the last one.
SQL> alter table SH.SALES move partition SALES_Q4_2003 tablespace USERS online parallel 8 update indexes;
Table altered.
That is to say, we use the last statement to rebuild all dependent indexes.
Modify Table Storage Attribute
For any new partitions in the future, we should change the storage attribute of the table.
SQL> alter table SH.SALES modify default attributes tablespace USERS;
Table altered.
The above statement uses modify_table_default_attrs clause conjuncts with segment_attributes_clause to form the useful SQL command.
By implementing the above two steps, we have moved the whole partitioned table to another tablespace.