Oracle按EVENT_TIME创建三范围分区及历史清理触发器实现求助

一、建表语句修正 #

你原有写法存在两个核心问题：

1. Oracle 不允许使用CURRENT_TIMESTAMP这类动态值作为静态分区的边界，建表时会直接报错
2. 仅定义了1个分区，不符合你要求的3个分区规则
   以下为符合需求的建表语句，默认周起始为当日，可根据实际业务调整日期计算逻辑：

CREATE TABLE EVENTS_LOG_TEST_PARTITION
(
    ID         NUMBER,
    METHOD     NVARCHAR2(100),
    INPUT      CLOB,
    EVENT_TIME TIMESTAMP(6),
    STATUS     NVARCHAR2(100),
    MESSAGE    NVARCHAR2(200)
)
PARTITION BY RANGE (EVENT_TIME)
(
    -- 历史分区：存储早于7天前的数据
    PARTITION P_HISTORY VALUES LESS THAN (TO_DATE(TO_CHAR(SYSDATE -7, 'yyyy-mm-dd'), 'yyyy-mm-dd')),
    -- 上周分区：存储7天内到当日0点前的数据
    PARTITION P_LAST_WEEK VALUES LESS THAN (TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd')),
    -- 当前周分区：存储当日起往后7天内的数据
    PARTITION P_CURRENT_WEEK VALUES LESS THAN (TO_DATE(TO_CHAR(SYSDATE +7, 'yyyy-mm-dd'), 'yyyy-mm-dd'))
)
ENABLE ROW MOVEMENT;

提示：如果需要周起始固定为周一，可把日期计算逻辑替换为TRUNC(SYSDATE, 'iw')，iw为ISO标准周的周起始参数。

二、分区滚动+历史数据自动清理实现 #

你提到的「每两周自动清理历史分区」不能用触发器实现：触发器是针对DML操作触发的逻辑，定时任务需要用Oracle内置的DBMS_SCHEDULER调度组件实现，实现逻辑如下：

1. 创建分区维护存储过程 #

负责历史数据清理、分区边界滚动，保证始终只有3个分区：

CREATE OR REPLACE PROCEDURE PROC_PARTITION_MAINTENANCE AS
    v_sql VARCHAR2(1000);
    v_current_week_bound DATE := TO_DATE(TO_CHAR(SYSDATE, 'yyyy-mm-dd'), 'yyyy-mm-dd');
    v_next_week_bound DATE := v_current_week_bound +7;
BEGIN
    -- 清理历史分区所有数据
    EXECUTE IMMEDIATE 'ALTER TABLE EVENTS_LOG_TEST_PARTITION TRUNCATE PARTITION P_HISTORY';
    
    -- 合并上周分区数据到历史分区
    EXECUTE IMMEDIATE 'ALTER TABLE EVENTS_LOG_TEST_PARTITION MERGE PARTITIONS P_HISTORY, P_LAST_WEEK INTO PARTITION P_HISTORY';
    
    -- 拆分当前周分区，生成新的上周、当前周分区
    v_sql := 'ALTER TABLE EVENTS_LOG_TEST_PARTITION SPLIT PARTITION P_CURRENT_WEEK AT ('''||TO_CHAR(v_current_week_bound, 'yyyy-mm-dd')||''') INTO (PARTITION P_LAST_WEEK, PARTITION P_CURRENT_WEEK)';
    EXECUTE IMMEDIATE v_sql;
    
    -- 重置当前周分区的上限为7天后
    v_sql := 'ALTER TABLE EVENTS_LOG_TEST_PARTITION SPLIT PARTITION P_CURRENT_WEEK AT ('''||TO_CHAR(v_next_week_bound, 'yyyy-mm-dd')||''') INTO (PARTITION P_CURRENT_WEEK, PARTITION P_TEMP)';
    EXECUTE IMMEDIATE v_sql;
    EXECUTE IMMEDIATE 'ALTER TABLE EVENTS_LOG_TEST_PARTITION DROP PARTITION P_TEMP';
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

2. 创建每两周执行一次的定时调度 #

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        JOB_NAME        => 'JOB_PARTITION_MAINTENANCE',
        JOB_TYPE        => 'STORED_PROCEDURE',
        JOB_ACTION      => 'PROC_PARTITION_MAINTENANCE',
        START_DATE      => SYSDATE,
        REPEAT_INTERVAL => 'FREQ=WEEKLY;INTERVAL=2', -- 每两周执行一次
        ENABLED         => TRUE,
        AUTO_DROP       => FALSE,
        COMMENTS        => '每两周清理历史分区，滚动日志表分区边界'
    );
END;
/

三、验证操作 #

执行完成后可以用以下语句查看分区配置是否符合预期：

SELECT PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'EVENTS_LOG_TEST_PARTITION';

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