Example - Adding a Partition to a RANGE Partitioned Table v11
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 | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00') Q2_2012 | FOR VALUES FROM ('01-APR-12 00:00:00') TO ('01-JUL-12 00:00:00') Q3_2012 | FOR VALUES FROM ('01-JUL-12 00:00:00') TO ('01-OCT-12 00:00:00') Q4_2012 | FOR VALUES FROM ('01-OCT-12 00:00:00') TO ('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 | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00') Q2_2012 | FOR VALUES FROM ('01-APR-12 00:00:00') TO ('01-JUL-12 00:00:00') Q3_2012 | FOR VALUES FROM ('01-JUL-12 00:00:00') TO ('01-OCT-12 00:00:00') Q4_2012 | FOR VALUES FROM ('01-OCT-12 00:00:00') TO ('01-JAN-13 00:00:00') Q1_2013 | FOR VALUES FROM ('01-JAN-13 00:00:00') TO ('01-APR-13 00:00:00') (5 rows)