Problems with Oracle partitioned tables adding partitions

Oracle range partition Table already has a maxvalue partition, how do I add a partition? You cannot delete the data in the MaxValue partition without affecting the online application.

1, create a partition table
Sql> CREATE TABLE P_range_test
2 (ID number,name varchar2 (100))
3 partition by range (ID) (
4 partition T_P1 values less than (10),
5 partition T_P2 values less than (20),
6 partition T_P3 values less than (30)
7);

Table created.

2, check the first step to create a good partition table information
Sql> Col table_name for A20
Sql> Col Partition_name for A20
Sql> Col High_value for A10
Sql> Col tablespace_name for A15
Sql> Select Table_name,partition_name,high_value,tablespace_name from User_tab_partitions where
Table_name= ' p_range_test ' ORDER by partition_position;
TABLE_NAME Partition_name High_value Tablespace_name
-------------------- -------------------- ---------- ---------------
P_range_test T_P1 TP1
P_range_test T_P2 TP1
P_range_test T_P3 TP1

As seen from above, there are 3 partitions

3, add a partition T_P4
Sql> ALTER TABLE P_range_test add partition T_P4 values less than (40);
Table altered.

Order add complete, of course this is not the LZ want it?

4, and then the third step to add a good partition information
Sql> Select Table_name,partition_name,high_value,tablespace_name from User_tab_partitions where
Table_name= ' p_range_test ' ORDER by partition_position;
TABLE_NAME Partition_name High_value Tablespace_name
-------------------- -------------------- ---------- ---------------
P_range_test T_P1 TP1
P_range_test T_P2 TP1
P_range_test T_P3 TP1
P_range_test T_P4 TP1

From the results of this experiment, you can see that the T_P4 partition has been successfully created

5. For this problem: If you specify MaxValue when you create a range partition table, you cannot add partitions (you need to use the split method to handle them)

Continue the experiment to verify whether it is feasible!!!

6, create a partition table with MaxValue
sql> drop table P_range_test purge;
CREATE TABLE P_range_test (ID number,name varchar2 (100))
Partition by range (ID) (
Partition T_P1 values less than (10),
Partition T_P2 values less than (20),
Partition T_P3 values less than (30),
Partition T_pmax values less than (MaxValue));
Table dropped.
Sql> 2 3 4 5 6
Table created.

7, at this time add partitions will be reported as the following error
Sql> ALTER TABLE P_range_test add partition T_P4 values less than (40);
ALTER TABLE P_range_test add partition T_P4 values less than (40)
*
ERROR at line 1:
Ora-14074:partition bound must collate higher than that's the last partition

Failed to add partition? There is no way to fix it, for the cow B Oracle must have a way, the treatment method is to use split to do!

8. Check the information of the current sub-table again
Sql> Select Table_name,partition_name,high_value,tablespace_name from User_tab_partitions where
Table_name= ' p_range_test ' ORDER by partition_position;
TABLE_NAME Partition_name High_value Tablespace_name
-------------------- -------------------- ---------- ---------------
P_range_test T_P1 TP1
P_range_test T_P2 TP1
P_range_test T_P3 TP1
P_range_test T_pmax MAXVALUE TP1

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/