Oracle range partition table already has maxvalue partition, how to add partition? It is not possible to delete data in the MaxValue partition without affecting the online application.

To do an experiment to illustrate the problem:
1. Create a partitioned 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)
D
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 the 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.
The order is added to complete, of course this is not the topic to be completed

4. Add the information of the partition in the third step
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 this experimental result you can see that the T_P4 partition has been created successfully

5. For questions: If you specify MaxValue when creating a range partition table, you cannot add partitions (you need to use the split method to handle them)
Continue the experiment to verify the feasibility!!!

6. Create a partitioned 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. The following error is reported when adding a partition at this time
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 oracle of Cow B there must be a way to deal with the method is to use split to engage!

8. Check the current sub-table 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_pmax MAXVALUE TP1

9. Note that this step is a key step!!!!! Use split to complete partition tasks not completed above
Sql> ALTER TABLE P_range_test split partition T_pmax at (+) into (partition, partition T_pmax);
Table altered.

10. Check the current sub-table 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 sys_p41 TP1
P_range_test T_pmax MAXVALUE TP1
Get it done!!!

Oracle range partition table already has maxvalue partition, how to add partition? It is not possible to delete data in the MaxValue partition without affecting the online application.