Add partitions to the oracle range Partition Table

To add partitions to an oracle range Partition Table, perform the experiment in two cases. 1. There is no maxvalue partition. 2. maxvalue partitions are available. The following example shows how to add partitions in the range partition table without maxvalue: www.2cto.com. 1. CREATE a partition table: SQL> CREATE TABLE t_range_part (ID NUMBER) 2 PARTITION BY RANGE (ID) 3 (4 PARTITION t_range_1 VALUES LESS THAN (10 ), 5 PARTITION t_range_2 values less than (20), 6 PARTITION t_range_3 values less than (30) 7); Table created www.2cto.com 2. view partition table information: SQL> select table_name, partition_name, high_value from dba_tab_partitions where table_name = 't_ RANGE_PART '; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ Define T_RANGE_PART T_RANGE_1 10 T_RANGE_PART T_RANGE_2 20 T_RANGE_PART T_RANGE_3 30 3. add partition: SQL> alter table t_range_part add partition t_range_4 values less than (40); Table altered 4. view partition table information again: SQL> select table_name, partition_name, high_value from dba_tab_part Itions where table_name = 't_ RANGE_PART '; TABLE_NAME PARTITION_NAME HIGH_VALUE when partition T_RANGE_PART limit 10 T_RANGE_PART T_RANGE_2 20 T_RANGE_PART T_RANGE_3 30 T_RANGE_PART T_RANGE_4 40 the partition is successfully added! B. Add partitions to partition tables with maxvalue partitions. 1. CREATE a partition table: SQL> CREATE TABLE t_range_part (ID NUMBER) 2 PARTITION BY RANGE (ID) 3 (4 PARTITION t_range_1 VALUES LESS THAN (10 ), 5 PARTITION t_range_2 values less than (20), 6 PARTITION t_range_3 values less than (30), 7 PARTITION t_range_max values less than (MAXVALUE) 8); Table created 2. view partition table information: SQL> select table_name, partition_name, high_value from dba_tab_partitions where table_name = 't_range_par T'; TABLE_NAME PARTITION_NAME HIGH_VALUE values ------------------------------ define T_RANGE_PART T_RANGE_1 10 T_RANGE_PART T_RANGE_2 20 T_RANGE_PART T_RANGE_3 30 T_RANGE_PART T_RANGE_MAX MAXVALUE 3. add partition: note that with maxvalue, you cannot directly add partition. Instead, you need to split the max partition. The following experiments: SQL> alter table t_range_part add partition t_range_4 values less than (40); alter table t_range_part add partition t_range_4 values less than (40) ORA-14074: the partition boundary must be adjusted to be greater than the last partition boundary SQL> alter table t_range_part split partition t_range_max at (40) into (partition t_range_4, partition t_range_max); Table altered www.2cto.com 4. view partition table information: SQL> select table_name, partition_name, high_value from dba_ta B _partitions where table_name = 't_ RANGE_PART '; TABLE_NAME PARTITION_NAME HIGH_VALUE when partition T_RANGE_PART limit 10 T_RANGE_PART Limit 20 T_RANGE_PART Limit 30 T_RANGE_PART limit 40 T_RANGE_PART T_RANGE_MAX MAXVALUE the partition is successfully added. For partition tables with maxvalue partitions, the last partition is actually cut. -- EOF