Example - Adding a Partition to a RANGE Partitioned Table v13

Suggest edits

The example that follows 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).

edb=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |      high_value
----------------+----------------------
 Q1_2012        | '01-APR-12 00:00:00'
 Q2_2012        | '01-JUL-12 00:00:00'
 Q3_2012        | '01-OCT-12 00:00:00'
 Q4_2012        | '01-JAN-13 00:00:00'
(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 invoking the command, the table includes the q1_2013 partition.

edb=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |      high_value
----------------+----------------------
 Q1_2012        | '01-APR-12 00:00:00'
 Q2_2012        | '01-JUL-12 00:00:00'
 Q3_2012        | '01-OCT-12 00:00:00'
 Q4_2012        | '01-JAN-13 00:00:00'
 Q1_2013        | '01-APR-13 00:00:00'
(5 rows)
← Prev

Example - Adding a Partition to a LIST Partitioned Table

↑ Up

ALTER TABLE...ADD PARTITION

Next →

Example - Adding a Partition with SUBPARTITIONS num...IN PARTITION DESCRIPTION