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.
edb=# SELECT partition_name, tablespace_name FROM ALL_TAB_PARTITIONS; partition_name | tablespace_name ----------------+----------------- Q1_2012 | Q2_2012 | Q3_2012 | Q4_2012 | TS_1 Q1_2013 | TS_2 (5 rows)
After preparing the target tablespace, invoke 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.
edb=# SELECT partition_name, tablespace_name FROM ALL_TAB_PARTITIONS; partition_name | tablespace_name ----------------+----------------- Q1_2012 | Q2_2012 | Q3_2012 | Q4_2012 | TS_1 Q1_2013 | TS_3 (5 rows)