The ALTER TABLE...ADD PARTITION command adds a partition to an existing partitioned table.

Overview

You can use the ALTER TABLE...ADD PARTITION command to add a partition to an existing partitioned table. Syntax: 
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}
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]

Description

The ALTER TABLE...ADD PARTITION command adds a partition to an existing partitioned table. The number of defined partitions in a partitioned table is not limited.

New partitions must be of the same type (LIST or RANGE) as existing partitions. The partitioning rules for new partitions must reference the same column specified in the partitioning rules that define the existing partitions.

You cannot use the ALTER TABLE...ADD PARTITION statement to add partitions to tables that have a MAXVALUE or DEFAULT rule. Alternatively, you can use the ALTER TABLE... SPLIT PARTITION statement to split an existing partition. This allows you to effectively increase 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 a tablespace in which a new partition will reside. If you do not specify a tablespace, the partition will be created in the default tablespace.

If the table is indexed, the index will be created on the new partition. To use the ALTER TABLE...ADD SUBPARTITION command, you must be the table owner, or have superuser (or administrative) privileges.

Parameters

Parameter Description
table_name The name (optionally schema-qualified) of the partitioned table.
partition_name The name of the partition to be created. Partition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
subpartition_name The name of the subpartition to be created. Subpartition names must be unique among all partitions and subpartitions, and must follow the naming conventions for object identifiers.
(value[, value]...) Use value to specify a quoted literal value (or a list of literal values separated by commas) by which table entries will be grouped into partitions. Each partitioning rule must specify at least one value, but the number of values specified in a rule is not limited. value may be null, default (if specifying a LIST partition), or maxvalue (if specifying a RANGE partition).

For more information about creating a default or maxvalue partition, see Handle stray values in a LIST or RANGE partitioned table.

tablespace_name The name of the tablespace in which the partition or subpartition resides.

Example - add a partition to a LIST partitioned table

The following example adds a partition to a list-partitioned table named sales. Run the following command to create the sales table: 
CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);
The table contains three partitions (americas, asia, and europe): 
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 americas       | 'US', 'CANADA'      
 asia           | 'INDIA', 'PAKISTAN' 
 europe         | 'FRANCE', 'ITALY'   
(3 rows)
The following command adds a partition named east_asia to the sales table: 
ALTER TABLE sales ADD PARTITION east_asia 
  VALUES ('CHINA', 'KOREA');
After this command is called, the table contains the east_asia partition: 
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 east_asia      | 'CHINA', 'KOREA'    
 americas       | 'US', 'CANADA'      
 asia           | 'INDIA', 'PAKISTAN' 
 europe         | 'FRANCE', 'ITALY'   
(4 rows)

Example - add a partition to a RANGE partitioned table

The following example adds a partition to a range-partitioned table named sales:

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')
);
The table contains four partitions (q1_2012, q2_2012, q3_2012, and q4_2012): 
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |  high_value   
----------------+---------------
 q4_2012        | '2013-Jan-01' 
 q3_2012        | '2012-Oct-01' 
 q2_2012        | '2012-Jul-01' 
 q1_2012        | '2012-Apr-01' 
(4 rows)
The following command adds a partition named q1_2013 to the sales table: 
ALTER TABLE sales ADD PARTITION q1_2013 
  VALUES LESS THAN('01-APR-2013');
After this command is called, the table contains the q1_2013 partition: 
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |  high_value   
----------------+---------------
 q1_2012        | '2012-Apr-01' 
 q2_2012        | '2012-Jul-01' 
 q3_2012        | '2012-Oct-01' 
 q4_2012        | '2013-Jan-01' 
 q1_2013        | '01-APR-2013' 
(5 rows)