The interval partition is actually a special range partition, so it's convenient to convert a range partition table into a interval partition table, and you can also convert a interval partition table to a range partition table.
For a normal range partition table:
sql> CREATE TABLE T_part
2 (ID number,
3 NAME VARCHAR2 (30),
4 create_date DATE)
5 PARTITION by RANGE (ID)
6 (PARTITION P1 VALUES less THAN (100),
7 PARTITION P2 VALUES less THAN (200),
8 PARTITION P3 VALUES less THAN (300));
Table has been created.
Sql> SELECT table_name, Partitioning_type, INTERVAL
2 from User_part_tables
3 WHERE table_name = ' t_part ';
TABLE_NAME PARTITION INTERVAL
------------------------------ --------- ----------------------------------------
T_part RANGE
Sql> INSERT into T_part
2 VALUES (+, ' A ', sysdate);
1 lines have been created.
Sql> INSERT into T_part
2 VALUES (A, ' A ', sysdate);
1 lines have been created.
Sql> INSERT into T_part
2 VALUES (360, ' TEST ', sysdate);
INSERT into T_part
*
Line 1th Error:
ORA-14400: The inserted partition keyword is not mapped to any partitions
Inserting a record that exceeds the upper partition limit will cause an error, and the following is a simple ALTER TABLE statement that converts a range partition table to a interval partition table:
sql> ALTER TABLE t_part SET INTERVAL (100);
The table has changed.
Sql> SELECT table_name, Partitioning_type, INTERVAL
2 from User_part_tables
3 WHERE table_name = ' t_part ';
TABLE_NAME PARTITION INTERVAL
------------------------------ --------- ----------------------------------------
T_part RANGE 100
Sql> INSERT into T_part
2 VALUES (360, ' TEST ', sysdate);
1 lines have been created.
Sql> COMMIT;
Submit completed.
Sql> SELECT table_name, partition_name, High_value
2 from User_tab_partitions
3 WHERE table_name = ' t_part ';
TABLE_NAME Partition_name High_value
------------------------------ ------------------------------ ----------------------
T_part P1 100
T_part P2 200
T_part P3 300
T_part SYS_P97 400
For interval partitioned tables, new data that exceeds the upper partition limit automatically causes the corresponding interval partition to be created.
The same interval partition table can be easily converted into a range partition table, simply enter the interval value:
sql> ALTER TABLE t_part SET INTERVAL ();