间隔分区报错ORA-14758
间隔分区报错ORA-14758
版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1443857
最近有个夜维，删除一张时间间隔分区表的历史分区，抛了异常ORA-14758: Last partition in the range section cannot be dropped
，如何解决？
首先模拟下操作，创建一张interval时间间隔分区表，按照"天"分区，插入属于两个分区的数据，其中一个占据了创建语句中指定的分区p1，另一个则会自动新建一个分区SYS_P61(系统自动创建)
SQL> create table tbl_partition ( 2 id number, 3 insert_time date 4 ) 5 partition by range (insert_time) 6 interval (numtodsinterval(1, 'day')) 7 ( 8 partition p1 values less than (to_date('2019-05-31','yyyy-mm-dd')) 9 ); Table created.SQL> insert into tbl_partition values(1, to_date('2019-05-30', 'yyyy-mm-dd'));1 row created.SQL> insert into tbl_partition values(1, to_date('2019-05-31', 'yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';TABLE_NAME PARTITION_NAME PARTITION_POSITION---------------- ------------------- ----------------------TBL_PARTITION P1 1TBL_PARTITION SYS_P61 2
Table created.
SQL> insert into tbl_partition values(1, to_date('2019-05-30', 'yyyy-mm-dd'));
1 row created.
SQL> insert into tbl_partition values(1, to_date('2019-05-31', 'yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
---------------- ------------------- ----------------------
TBL_PARTITION P1 1
TBL_PARTITION SYS_P61 2此时删除p1，会提示ORA-14758: Last partition in the range section cannot be dropped，禁止删除该分区，
SQL> alter table tbl_partition drop partition p1; alter table tbl_partition drop partition p1 *ERROR at line 1:ORA-14758: Last partition in the range section cannot be dropped
alter table tbl_partition drop partition p1
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped难道是创建语句中的分区，不能手动删除？
重建分区表，此时指定2个分区，插入3条数据，此时含3个分区(2个是指定的，1个是系统创建)，可以看出，手动删除partition_position=2的分区p2，是被禁止的，但是删除partition_position=1的分区p1，是可以删除，此时p2分区的partition_position就改为了1，其他分区的partition_position依次减1，再插入属于原p1分区的数据，并不会自动创建新的分区，而是进入原p2分区，从逻辑上，讲得通，例如原p1分区，条件是<5月31日，原p2分区，条件是<6月1日，5月30日的数据，应该插入原p1分区，5月31日的数据，应该插入原p2分区，此时删除了p1，再次插入5月30日的数据，会进入原p2分区，因此他符合条件，<6月1日，
SQL> create table tbl_partition ( 2 id number, 3 insert_time date 4 ) 5 partition by range (insert_time) 6 interval (numtodsinterval(1, 'day')) 7 ( 8 partition p1 values less than (to_date('2019-05-31','yyyy-mm-dd')), 9 partition p2 values less than (to_date('2019-06-01','yyyy-mm-dd')) 10 );Table created.SQL> insert into tbl_partition values(1, to_date('2019-05-30','yyyy-mm-dd')); 1 row created.SQL> insert into tbl_partition values(2, to_date('2019-05-31','yyyy-mm-dd')); 1 row created.SQL> insert into tbl_partition values(2, to_date('2019-06-01','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';TABLE_NAME PARTITION_NAME PARTITION_POSITION------------------ ------------------- -----------------------TBL_PARTITION P1 1TBL_PARTITION P2 2TBL_PARTITION SYS_P62 3SQL> alter table tbl_partition drop partition p2; alter table tbl_partition drop partition p2 *ERROR at line 1:ORA-14758: Last partition in the range section cannot be droppedSQL> alter table tbl_partition drop partition p1;Table altered.SQL> select * from tbl_partition; ID INSERT_TIME---------- ------------------ 2 31-MAY-19 2 01-JUN-19SQL> insert into tbl_partition values(1, to_date('2019-05-30','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';TABLE_NAME PARTITION_NAME PARTITION_POSITION------------------ -------------------- ------------------------TBL_PARTITION P2 1TBL_PARTITION SYS_P62 2
Table created.
SQL> insert into tbl_partition values(1, to_date('2019-05-30','yyyy-mm-dd'));
1 row created.
SQL> insert into tbl_partition values(2, to_date('2019-05-31','yyyy-mm-dd'));
1 row created.
SQL> insert into tbl_partition values(2, to_date('2019-06-01','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------ ------------------- -----------------------
TBL_PARTITION P1 1
TBL_PARTITION P2 2
TBL_PARTITION SYS_P62 3
SQL> alter table tbl_partition drop partition p2;
alter table tbl_partition drop partition p2
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped
SQL> alter table tbl_partition drop partition p1;
Table altered.
SQL> select * from tbl_partition;
ID INSERT_TIME
---------- ------------------
2 31-MAY-19
2 01-JUN-19
SQL> insert into tbl_partition values(1, to_date('2019-05-30','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------ -------------------- ------------------------
TBL_PARTITION P2 1
TBL_PARTITION SYS_P62 2从现象看，创建语句中partition_position值最高的分区，禁止手动删除。
这篇MOS给出了不能删除last分区的原因，因为在间隔分区中自动创建的分区会依赖于创建语句中作为参考的last分区，因此last分区，不能手动删除，但是创建语句中，last之前的分区，是可以正常删除，
ORA-14758: Last Partition In The Range Section Cannot Be Dropped (Doc ID 882681.1)In case of interval partitioning the partitions which are created automatically depends on the last partition created as a reference while creating a table. So the last partition can not be dropped.
这种行为是正常的，毕竟在范围段内，last分区的最高值对于间隔分区很重要，这个值通常称为间隔分区开始的过渡点，
This is expected behaviour. The high value of the last partition in the range section has an important role in the interval partition table. It is called the transition point where the interval partition section start, hence the last partition in the range section cannot be dropped.
能有什么方法删除这个分区？
其中一种方法，就是临时删除interval，再重新指定interval，此时创建语句中last分区就可以正常删除，但是自动创建的下一个分区，此时就成了last分区，
SQL> alter table tbl_partition set interval();Table altered.SQL> alter table tbl_partition set interval(numtodsinterval(1, 'day'));Table altered.SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';TABLE_NAME PARTITION_NAME PARTITION_POSITION----------------- -------------------- ------------------------TBL_PARTITION P2 1TBL_PARTITION SYS_P62 2SQL> alter table tbl_partition drop partition p2;Table altered.SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';TABLE_NAME PARTITION_NAME PARTITION_POSITION----------------- -------------------- -----------------------TBL_PARTITION SYS_P62 1SQL> insert into tbl_partition values(1, to_date('2019-05-30','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.SQL> select * from tbl_partition; ID INSERT_TIME---------- ------------------ 2 01-JUN-19 1 30-MAY-19SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';TABLE_NAME PARTITION_NAME PARTITION_POSITION------------------ ------------------- -----------------------TBL_PARTITION SYS_P62 1SQL> alter table tbl_partition drop partition sys_p62;alter table tbl_partition drop partition sys_p62 *ERROR at line 1:ORA-14758: Last partition in the range section cannot be dropped
Table altered.
SQL> alter table tbl_partition set interval(numtodsinterval(1, 'day'));
Table altered.
SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
----------------- -------------------- ------------------------
TBL_PARTITION P2 1
TBL_PARTITION SYS_P62 2
SQL> alter table tbl_partition drop partition p2;
Table altered.
SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
----------------- -------------------- -----------------------
TBL_PARTITION SYS_P62 1
SQL> insert into tbl_partition values(1, to_date('2019-05-30','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tbl_partition;
ID INSERT_TIME
---------- ------------------
2 01-JUN-19
1 30-MAY-19
SQL> select table_name, partition_name, partition_position from user_tab_partitions where table_name='TBL_PARTITION';
TABLE_NAME PARTITION_NAME PARTITION_POSITION
------------------ ------------------- -----------------------
TBL_PARTITION SYS_P62 1
SQL> alter table tbl_partition drop partition sys_p62;
alter table tbl_partition drop partition sys_p62
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped这种方法的弊端，需要NOWAIT对表加EXCLUSIVE锁，影响正常交易，如果此时，有其他交易进行，alter table则会抛ORA-00054，但是所有全局和局部索引，依然可用。
这两篇文章，
How To Avoid ORA-14758 in Interval Partitioned Table (Doc ID 1526571.1) How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)
给出了关于避免ORA-14758的所有workaround，原则就是there must be a partition in the range section with the high value of the original last partition in the range section，
(i) You can split an interval partition (a partition in the interval section that is materialized already). (ii) You can merge two adjacent interval partitions into one partition. (iii) You can merge the first interval partition with the highest range partition. (iv) Temporarily disable the interval partition.
从原理层，和实践层，了解ORA-14758的根本原因，才会有助于我们更好地理解Oracle对间隔分区的设计，更好地运用他。