Use the following syntax to add or drop partitions to or from a partitioned table:

Syntax

ALTER TABLE table name
 [ADD
  [(]
      PARTITION 
     [partition name] VALUES { <= | IN } (column_values)
      [ RO_RW_ATTRIBUTE ][area_spec]
      [ PRO_DESCRIPTION desc], 
      [PARTITION [partition name] VALUES { <= |IN } (column_values)
      [area_spec] [ PRO_DESCRIPTION desc],...] 
  [)]
 ] 
 [ DROP { PARTITION | PARTITIONS } partition name, 
     [partition name, ...] ]

Parameters

column_values
Uses the following syntax:
column_value[ , column_value, ...]
column_value

Uses a constant as its value

RO_RW_ATTRIBUTE

Uses the following syntax and marks the partition as a read-only partition or a read-write partition:

READ_ONLY | READ_WRITE

If RO_RW_Attribute is not specified, then by default, the partition is marked as a read-write partition.

Notes

The following semantics apply when using ALTER TABLE to add and drop partitions:

  • The table specified in the syntax must be a partitioned table
  • In case of a table partitioned by LIST, the new partition must be specified with key values that are not already defined
  • In case of a table partitioned by RANGE with data, the new RANGE partition can be added with a RANGE key value greater than the existing maximum key value of the table
  • If the table is empty, new RANGE partitions can be added within the defined partition key values
  • The partition to be dropped must be empty and must not be allocated
  • The database truncate utility must be used to remove data from the partition and no allocation must be assigned to it before executing the ALTER TABLE DROP syntax
  • The last partition can also be dropped
  • To drop a partition, the table must contain the partition name specified
  • To drop multiple partitions, the partition names must be specified in a comma-separated list and the table must contain those partitions
  • Dropping a partition is not allowed when split, merge, or other database utilities are operating on a partition
  • Dropping a partition which is referring to the initial composite partition is not allowed
  • Dropping a partition is not allowed when it is marked as ready to be split
  • Dropping a partition is not allowed when it is the source for a split operation
  • A read-only partition cannot be deallocated, hence cannot be dropped.

Examples

Adding RANGE partitions

The following example illustrates adding RANGE partitions. 
CREATE TABLE Pub.tpCustRange
 (
  Custid int,
  Custname varchar (50),
  Salary int
 ) 
 PARTITION BY RANGE custid
 USING TABLE AREA "Customer Table Area"
 (
 PARTITION partn20k VALUES <= (20000),
 PARTITION partn50k VALUES <= (50000),
 PARTITION partn80k VALUES <= (80000),
 );
The below statement adds a partition with values <= 30000.
ALTER TABLE Pub.tpCustRange
ADD PARTITION partn20k VALUES <= 30000;
Note: Adding new partition whose key values are less than the existing RANGE key values is allowed since the table does not contain any data.
The below statement adds two partitions whose values are <= 40000 and <= 90000, respectively:
ALTER TABLE Pub.tpCustRange
ADD
 (PARTITION partn40k VALUES <= 40000,
  PARTITION partn90k VALUES <= 90000,);

Adding partitions to a subpartitioned table

The below example shows how to add partitions to a subpartitioned table:
CREATE TABLE Pub.tpsub
 (
   a int,
  b int,
  c int,
  d int
 )
  PARTITION BY LIST a
  SUBPARTITION BY LIST b
  SUBPARTITION BY RANGE c	
  USING TABLE AREA "Tenant 1 table Area"
  (  
   PARTITION  sub_a values <= (1,1,100),
   PARTITION  sub_b values <= (2,2,200),
   PARTITION  sub_c values <= (3,3,100),
   PARTITION  sub_d values <= (3,3,200),
   PARTITION  sub_e values <= (3,3,300)
  );

ALTER TABLE Pub.tpsub
 ADD ( PARTITION sub2_e_1 VALUES <= (3,3,400),
       PARTITION sub2_e_2 VALUES <= (3,3,500))

Adding LIST partitions

The following example illustrates adding LIST partitions: 
CREATE TABLE Pub.tpCustList
 (
  Custid int,
  Custname varchar (50),
  City varchar (50),
  join_date date,
  Salary int
 ) 
  PARTITION BY LIST city
  USING TABLE AREA "Customer Table Area"
  (
  PARTITION p1 VALUES IN ( 'Atlanta' ),
  PARTITION p2 VALUES IN ( 'Montgomery' ),
  PARTITION p3 VALUES IN ( 'Boston' )
  );
The below statement adds two new partitions: 
ALTER TABLE Pub.tpCustList
 ADD
 (
  PARTITION p4 VALUES IN ( 'Jacksonville' ),
  PARTITION p5 VALUES IN ( 'Chicago')
 );

Adding read-only partitions

The following example illustrates adding a read-only partition: 
ALTER TABLE Pub.TPCustomer ADD PARTITION cust04 VALUES IN (40, 41, 42) READ_ONLY;

Adding read-write partitions

The following example illustrates adding a read-write partition: 
ALTER TABLE Pub.TPCustomer ADD PARTITION cust06 VALUES IN (50, 51, 52) READ_WRITE;

Dropping partitions

The following example illustrates dropping a single partition: 
ALTER TABLE Pub.tpCustList
DROP PARTITIONS p4;
The following example illustrates dropping multiple partitions: 
ALTER TABLE Pub.tpCustList
DROP PARTITIONS p4, p5;