Table of Contents Previous Next



Use the ALTER TABLEADD PARTITION command to add a partition to an existing partitioned table. The syntax is:
ALTER TABLE table_name ADD PARTITION partition_definition;
Where partition_definition is:
{list_partition | range_partition }
and list_partition is:
PARTITION [partition_name]
VALUES (value[, value]...)
[TABLESPACE tablespace_name]
[(subpartition, ...)]
and range_partition is:
PARTITION [partition_name]
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
[(subpartition, ...)]
Where subpartition is:
{list_subpartition | range_subpartition | hash_subpartition}
and list_subpartition is:
SUBPARTITION [subpartition_name]
VALUES (value[, value]...)
[TABLESPACE tablespace_name]
and range_subpartition is:
SUBPARTITION [subpartition_name ]
VALUES LESS THAN (value[, value]...)
[TABLESPACE tablespace_name]
The ALTER TABLE… ADD PARTITION command adds a partition to an existing partitioned table. There is no upper limit to the number of defined partitions in a partitioned table.
New partitions must be of the same type (LIST, RANGE or HASH) as existing partitions. The new partition rules must reference the same column specified in the partitioning rules that define the existing partition(s).
You cannot use the ALTER TABLEADD PARTITION statement to add a partition to a table with a MAXVALUE or DEFAULT rule. Note that you can alternatively use the ALTER TABLESPLIT PARTITION statement to split an existing partition, effectively increasing the number of partitions in a table.
RANGE partitions must be specified in ascending order. You cannot add a new partition that precedes existing partitions in a RANGE partitioned table.
Include the TABLESPACE clause to specify the tablespace in which the new partition will reside. If you do not specify a tablespace, the partition will reside in the default tablespace.
To use the ALTER TABLE... ADD PARTITION command you must be the table owner, or have superuser (or administrative) privileges.
(value[, value]...)
Use value to specify a quoted literal value (or comma-delimited list of literal values) by which rows will be distributed into partitions. Each partitioning rule must specify at least one value, but there is no limit placed on the number of values specified within a rule. value may also be NULL, DEFAULT (if specifying a LIST partition), or MAXVALUE (if specifying a RANGE partition).
For information about creating a DEFAULT or MAXVALUE partition, see Section 13.4.
The example that follows adds a partition to the list-partitioned sales table. The table was created using the command:
The table contains three partitions (americas, asia, and europe) :
The table contains four partitions (q1_2012, q2_2012, q3_2012, and q4_2012):


Table of Contents Previous Next