You can use the ALTER TABLE...MOVE PARTITION command to move a partition or subpartition to a different tablespace.

Overview

The ALTER TABLE...MOVE PARTITION command has two forms.
  • The first form is to move a partition to a new tablespace: 
    ALTER TABLE table_name 
  MOVE PARTITION partition_name 
   TABLESPACE tablespace_name;
  • The second form is to move a subpartition to a new tablespace: 
    ALTER TABLE table_name 
  MOVE SUBPARTITION subpartition_name 
   TABLESPACE tablespace_name;
The syntax of the ALTER TABLE...MOVE PARTITION command makes no distinctions between a partition and a subpartition:
  • You can move a partition by using the MOVE PARTITION or MOVE SUBPARTITION clause.
  • You can move a subpartition by using the MOVE PARTITION or MOVE SUBPARTITION clause.

Description

The ALTER TABLE...MOVE PARTITION command moves a partition or subpartition from its current tablespace to a different tablespace. You must own a table to call ALTER TABLE...MOVE PARTITION or ALTER TABLE...MOVE SUBPARTITION.

Parameters

Parameter Description
table_name The name (optionally schema-qualified) of the table in which the partition resides.
partition_name The name of the partition or subpartition to be moved.
tablespace_name The name of the tablespace to which the partition or subpartition will be moved.

Example - move a partition to a different tablespace

The following example moves a partition of the sales table from one tablespace to another. First, run the following command to create the sales table: 
CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 VALUES LESS THAN ('2012-Apr-01'),
  PARTITION q2_2012 VALUES LESS THAN ('2012-Jul-01'),
  PARTITION q3_2012 VALUES LESS THAN ('2012-Oct-01'),
  PARTITION q4_2012 VALUES LESS THAN ('2013-Jan-01') TABLESPACE ts_1,
  PARTITION q1_2013 VALUES LESS THAN ('2013-Mar-01') TABLESPACE ts_2
);
Querying the ALL_TAB_PARTITIONS view confirms that the partitions reside on the expected servers and tablespaces: 
acctg=# SELECT partition_name, tablespace_name FROM ALL_TAB_PARTITIONS;
 partition_name | tablespace_name 
----------------+-------------+-----------------
 q1_2013        | ts_2
 q4_2012        | ts_1
 q3_2012        | 
 q2_2012        | 
 q1_2012        | 
(5 rows)
After preparing the target tablespace, call the ALTER TABLE...MOVE PARTITION command to move the q1_2013 partition from a tablespace named ts_2 to a tablespace named ts_3: 
ALTER TABLE sales MOVE PARTITION q1_2013 TABLESPACE ts_3;
Querying the ALL_TAB_PARTITIONS view shows that the move was successful: 
acctg=# SELECT partition_name, tablespace_name FROM ALL_TAB_PARTITIONS;
 partition_name | tablespace_name 
----------------+-----------------
 q1_2013        | ts_3
 q4_2012        | ts_1
 q3_2012        | 
 q2_2012        | 
 q1_2012        | 
(5 rows)