Adding Multiple Partitions

You can add multiple new partitions and subpartitions with the ADD PARTITION and ADD SUBPARTITION clauses of the ALTER TABLE statement. When adding multiple partitions, local and global index operations are the same as when adding a single partition. Adding multiple partitions and subpartitions is only supported for range, list, and system partitions and subpartitions.

You can add multiple range partitions that are listed in ascending order of their upper bound values to the high end (after the last existing partition) of a range-partitioned or composite range-partitioned table, provided the MAXVALUE partition is not defined. Similarly, you can add multiple list partitions to a table using new sets of partition values if the DEFAULT partition does not exist.

Multiple system partitions can be added using a single SQL statement by specifying the individual partitions. For example, the following SQL statement adds multiple partitions to the range-partitioned sales table created in Example 4-1:

ALTER TABLE sales ADD 
  PARTITION sales_q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy')),
  PARTITION sales_q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','dd-MON-yyyy')),
  PARTITION sales_q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','dd-MON-yyyy')),
  PARTITION sales_q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy'))
;

You can use the BEFORE clause to add multiple new system partitions in relation to only one existing partition. The following SQL statements provide an example of adding multiple individual partitions using the BEFORE clause:

CREATE TABLE system_part_tab1 (number1 integer, number2 integer) 
PARTITION BY SYSTEM
( PARTITION p1,
  PARTITION p2,
  PARTITION p3,
  PARTITION p_last);

ALTER TABLE system_part_tab1 ADD 
  PARTITION p4,
  PARTITION p5,
  PARTITION p6
  BEFORE PARTITION p_last;

SELECT SUBSTR(TABLE_NAME,1,18) table_name, TABLESPACE_NAME, 
   SUBSTR(PARTITION_NAME,1,16) partition_name 
   FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SYSTEM_PART_TAB1';TABLE_NAME         TABLESPACE_NAME                PARTITION_NAME
------------------ ------------------------------ ----------------
SYSTEM_PART_TAB1   USERS                          P_LAST
SYSTEM_PART_TAB1   USERS                          P6
SYSTEM_PART_TAB1   USERS                          P5
SYSTEM_PART_TAB1   USERS                          P4
SYSTEM_PART_TAB1   USERS                          P3
SYSTEM_PART_TAB1   USERS                          P2
SYSTEM_PART_TAB1   USERS                          P1