The difference between the Oracle Range partition table and the interval partition table for the split partition

The Range partition table and the interval partition table can be converted to each other, but there is a difference. For example, in the split partition, the range partition table is not restricted, and the interval partition table may be an error.

First look at the example of a range partition:

sql> CREATE TABLE T_part_range

2 (ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (create_date)

6 (PARTITION P1 VALUES less THAN (to_date (' 2009-1 ', ' yyyy-mm ')),

7 PARTITION P2 VALUES less THAN (to_date (' 2009-5 ', ' yyyy-mm ')),

8 PARTITION P3 VALUES less THAN (to_date (' 2009-9 ', ' yyyy-mm '));

Table has been created.

sql> ALTER TABLE T_part_range

2 SPLIT PARTITION P3

3 at (to_date (' 2009-7-30 ', ' yyyy-mm-dd '))

4 into (PARTITION P3, PARTITION P4);

The table has changed.

Sql> SELECT Partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' T_part_range '

4 ORDER by 1;

Partition_name High_value

-------------- ----------------------------------------------------------------------------------

P1 to_date (' 2009-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

P2 to_date (' 2009-05-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

P3 to_date (' 2009-07-30 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

P4 to_date (' 2009-09-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

The same operation, when executed on the interval partition table, will have an error:

sql> CREATE TABLE T_part_inter

2 (ID number,

3 NAME VARCHAR2 (30),

4 create_date DATE)

5 PARTITION by RANGE (create_date)

6 INTERVAL (INTERVAL ' 4 ' MONTH)

7 (PARTITION P1 VALUES less THAN (to_date (' 2009-1 ', ' yyyy-mm ')),

8 PARTITION P2 VALUES less THAN (to_date (' 2009-5 ', ' yyyy-mm '));

Table has been created.

Sql> INSERT into T_part_inter

2 VALUES (1, ' TEST ', to_date (' 2009-8 ', ' yyyy-mm '));

1 lines have been created.

Sql> COMMIT;

Submit completed.

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

Sql> SELECT Partition_name, High_value

2 from User_tab_partitions

3 WHERE table_name = ' t_part_inter '

4 ORDER by 1;

Partition_name High_value

-------------- ----------------------------------------------------------------------------------

P1 to_date (' 2009-01-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

P2 to_date (' 2009-05-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

Sys_p96 to_date (' 2009-09-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')

sql> ALTER TABLE T_part_range

2 SPLIT PARTITION for (to_date (' 2009-8 ', ' yyyy-mm '))

3 at (to_date (' 2009-7-30 ', ' yyyy-mm-dd '))

4 into (PARTITION P3, PARTITION P4);

ALTER TABLE T_part_range

*

Line 1th Error:

ORA-14080: Unable to split partition by the specified upper limit

The obvious cause of the problem is that after the split partition operation, the interval partition is interval incremented based on the date of the split, so the log with a value greater than 28th interval will cause an error in February. Oracle therefore prohibits a date greater than 28th as the base partition for the interval month.

Author: 51cto Blog Oracle Little Bastard