13.3.7 ALTER TABLE… MOVE PARTITION

Table of Contents Previous Next



Use the ALTER TABLEMOVE PARTITION command to move a partition or subpartition 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 sub
partition_name
TABLESPACE
tablespace_name;
You can move a partition with the MOVE PARTITION or MOVE SUBPARTITION clause.
You can move a subpartition with MOVE PARTITION or MOVE SUBPARTITION clause.
The ALTER TABLEMOVE PARTITION command moves a partition or subpartition from its current tablespace to a different tablespace. The ALTER TABLEMOVE PARTITION command can move partitions (or subpartitions) of a LIST, RANGE or HASH partitioned (or subpartitioned) table. You must own a 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