Table of Contents Previous Next



Use the ALTER TABLEMOVE PARTITION command to move a partition to a different tablespace. The command takes two forms.
ALTER TABLE table_name
MOVE PARTITION
partition_name
TABLESPACE
tablespace_name;
ALTER TABLE table_name
MOVE SUBPARTITION
subpartition_name
TABLESPACE
tablespace_name;
The ALTER TABLEMOVE PARTITION command moves a partition from its current tablespace to a different tablespace. The ALTER TABLEMOVE PARTITION command can move partitions of a LIST, RANGE or HASH partitioned table.
The same behavior as previously described applies for the subpartition_name used with the MOVE SUBPARTITION clause.
You must own the table to invoke ALTER TABLEMOVE PARTITION or ALTER TABLE MOVE SUBPARTITION.
The following example moves a partition of the sales table from one tablespace to another. First, create the sales table with the command:
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:
After preparing the target tablespace, invoke the ALTER TABLEMOVE 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:


Table of Contents Previous Next