Skip to content
Home » Oracle Database » Alter Table Move Partition to Another Tablespace

Alter Table Move Partition to Another Tablespace

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.

  1. Move Single Partition Segments
  2. Move All Partition Segments

Move Single Partition Segments

To defragment one single partition, we have to know which table partition is a good candidate to move.

Move All Partition Segments

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.

  1. ALTER TABLE MOVE PARTITION
  2. ALTER TABLE MOVE PARTITION ONLINE
  3. ALTER TABLE MOVE PARTITION PARALLEL
  4. ALTER TABLE MOVE PARTITION UPDATE INDEXES
  5. Modify Table Storage Attribute

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;

Let's see the statements.

alter table SH.SALES move partition SALES_1995 tablespace USERS online;
alter table SH.SALES move partition SALES_1996 tablespace USERS online;
alter table SH.SALES move partition SALES_H1_1997 tablespace USERS online;

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;

Let's see the statements.

alter table SH.SALES move partition SALES_1995 tablespace USERS online parallel 8;
alter table SH.SALES move partition SALES_1996 tablespace USERS online parallel 8;
alter table SH.SALES move partition SALES_H1_1997 tablespace USERS online parallel 8;

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.

Leave a Reply

Your email address will not be published. Required fields are marked *