You can redefine a partition by using the REORGANIZE PARTITION clause of the ALTER statement. By redefining partitions, you can combine multiple partitions into one or divide one into multiple.
ALTER {TABLE | CLASS} <table_name>
REORGANIZE PARTITION
<alter partition name comma list>
INTO ( <partition definition comma list> )
partition definition comma list:
PARTITION <partition_name> VALUES LESS THAN ( <range_value> ),....
The following example shows how to perform repartitioning the before_2000 partition into the before_1996 and before_2000 partitions.
CREATE TABLE participant2 ( host_year INT, nation CHAR(3), gold INT, silver INT, bronze INT)
PARTITION BY RANGE (host_year)
( PARTITION before_2000 VALUES LESS THAN (2000),
PARTITION before_2008 VALUES LESS THAN (2008) );
ALTER TABLE participant2 REORGANIZE PARTITION before_2000 INTO (
PARTITION before_1996 VALUES LESS THAN (1996),
PARTITION before_2000 VALUES LESS THAN (2000)
);
The following example shows how to combine two partitions redefined in Example 1 back into a single before_2000 partition.
ALTER TABLE participant2 REORGANIZE PARTITION before_1996, before_2000 INTO
(PARTITION before_2000 VALUES LESS THAN (2000) );