How Oracle Move Partitioned Table

  • by

ALTER TABLE MOVE PARTITION

A partitioned table is one type of table which data is divided into several segments. Segment is different from schema object, which 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, you receive error ORA-14511.

There're two major steps to move a partitioned table to another tablespace completely.

  1. Move All Partition Segments
  2. Modify Table Storage Attribute

A. 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.

1. Compose Executable SQL Statements

In this case, we'd like to move a partitioned table SH.SALES to another tablespace.

SQL> column stmts format a90;
SQL> set pagesize 100;
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;
alter table SH.SALES move partition SALES_H2_1997 tablespace USERS;
alter table SH.SALES move partition SALES_Q1_1998 tablespace USERS;
alter table SH.SALES move partition SALES_Q1_1999 tablespace USERS;
alter table SH.SALES move partition SALES_Q1_2000 tablespace USERS;
alter table SH.SALES move partition SALES_Q1_2001 tablespace USERS;
alter table SH.SALES move partition SALES_Q1_2002 tablespace USERS;
alter table SH.SALES move partition SALES_Q1_2003 tablespace USERS;
alter table SH.SALES move partition SALES_Q2_1998 tablespace USERS;
alter table SH.SALES move partition SALES_Q2_1999 tablespace USERS;
alter table SH.SALES move partition SALES_Q2_2000 tablespace USERS;
alter table SH.SALES move partition SALES_Q2_2001 tablespace USERS;
alter table SH.SALES move partition SALES_Q2_2002 tablespace USERS;
alter table SH.SALES move partition SALES_Q2_2003 tablespace USERS;
alter table SH.SALES move partition SALES_Q3_1998 tablespace USERS;
alter table SH.SALES move partition SALES_Q3_1999 tablespace USERS;
alter table SH.SALES move partition SALES_Q3_2000 tablespace USERS;
alter table SH.SALES move partition SALES_Q3_2001 tablespace USERS;
alter table SH.SALES move partition SALES_Q3_2002 tablespace USERS;
alter table SH.SALES move partition SALES_Q3_2003 tablespace USERS;
alter table SH.SALES move partition SALES_Q4_1998 tablespace USERS;
alter table SH.SALES move partition SALES_Q4_1999 tablespace USERS;
alter table SH.SALES move partition SALES_Q4_2000 tablespace USERS;
alter table SH.SALES move partition SALES_Q4_2001 tablespace USERS;
alter table SH.SALES move partition SALES_Q4_2002 tablespace USERS;
alter table SH.SALES move partition SALES_Q4_2003 tablespace USERS;

28 rows selected.

2. 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.
...

3. ONLINE Clause

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> 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.

B. 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 *