Oracle间隔分区表无法删除最后一个静态分区的问题及解决

表分区定义 #

现有一张按expiration_time字段按月范围分区的Oracle表，采用静态分区+动态间隔分区的方式，分区定义如下：

PARTITION BY RANGE (expiration_time) INTERVAL (NUMTOYMINTERVAL(1, 'month')) (
  PARTITION p_2021_10 VALUES LESS THAN (TO_DATE('01-11-2021', 'DD-MM-YYYY')),
  PARTITION p_2021_11 VALUES LESS THAN (TO_DATE('01-12-2021', 'DD-MM-YYYY')),
  PARTITION p_2021_12 VALUES LESS THAN (TO_DATE('01-01-2022', 'DD-MM-YYYY')),
  PARTITION p_2022_01 VALUES LESS THAN (TO_DATE('01-02-2022', 'DD-MM-YYYY')),
  PARTITION p_2022_02 VALUES LESS THAN (TO_DATE('01-03-2022', 'DD-MM-YYYY'))
...

静态分区之后的分区由Oracle自动动态创建，示例如下：

P_2022_02   TIMESTAMP' 2022-03-01 00:00:00' <<<< Last statically created partition
SYS_P18684  TIMESTAMP' 2022-04-01 00:00:00' <<<< Dynamic partition 1
SYS_P18902  TIMESTAMP' 2022-05-01 00:00:00' <<<< Dynamic partition 2
SYS_P19364  TIMESTAMP' 2022-06-01 00:00:00' <<<< Dynamic partition 3
...

删除分区报错 #

尝试删除最后一个静态分区p_2022_02时执行以下语句：

ALTER TABLE TRANSACTION_LOG DROP PARTITION p_2022_02 UPDATE INDEXES

触发报错：

Error report -
ORA-14758: Last partition in the range section cannot be dropped
14758. 00000 -  "Last partition in the range section cannot be dropped"
*Cause:    An attempt was made to drop the last range partition of an interval
           partitioned table.
*Action:   Do not attempt to drop this partition.

限制说明 #

Oracle间隔分区表的机制要求，必须保留范围分区段的最后一个分区，即便后续已有自动创建的间隔分区，也不允许删除该最后一个静态范围分区。

变通解决方案 #

1. 修改清理存储过程，排除最后一个静态分区 #

通过在清理逻辑中添加条件，跳过最后一个静态分区p_2022_02，只删除其他早于当前日期的过期分区，存储过程代码如下：

create or replace PROCEDURE REMOVE_OBSOLETE_PARTITIONS IS BEGIN

BEGIN
    DECLARE CURSOR OBSOLETE_PARTITIONS IS
    SELECT PARTITION_NAME FROM (
      SELECT PARTITION_NAME,
            TO_DATE(substr(
                        extractvalue(dbms_xmlgen.getxmltype(
                                            'select high_value FROM USER_TAB_PARTITIONS WHERE table_name = '''
                                            || t.table_name || ''' and PARTITION_NAME = '''
                                            || t.partition_name || ''''
                                        ), '//text()'
                        ), 12, 10),
                    'YYYY-MM-DD') AS high_value
      FROM USER_TAB_PARTITIONS t
      WHERE TABLE_NAME = 'TRANSACTION_LOG' AND t.partition_name != 'P_2022_02')
    WHERE high_value < sysdate;

    BEGIN
        FOR result IN OBSOLETE_PARTITIONS LOOP
            EXECUTE IMMEDIATE ('ALTER TABLE TRANSACTION_LOG DROP PARTITION ' || result.PARTITION_NAME || ' UPDATE INDEXES');
        END LOOP;
    END;
END;

END REMOVE_OBSOLETE_PARTITIONS;

2. 处理大数据量的最后静态分区 #

若p_2022_02分区内数据量较大，无法直接删除，可对其执行截断操作（TRUNCATE PARTITION）来释放空间，而非删除分区。

3. 预先规划默认分区 #

建议在创建表时预先创建一个空的默认分区，并将其永久排除在清理逻辑外，避免后续出现类似的分区删除限制问题。

内容的提问来源于stack exchange，提问作者Filip