最简单的解决方案是指定间隔分区，它将为新日期动态分配指定大小的新分区。分区修剪非常适合where条件，甚至子查询，并允许基于条件的动态分区选择。
但是，如果出于某种原因，您仍然需要在过去三个月内使用动态上限的单个分区，则需要安排每日作业，以相应地调整现有分区。它应：
1.为新日期添加新分区，以使用其上边界作为新的上边界。
1.将这个新分区与“旧”的最后一个分区合并。
1.在日期前3个月分割最后一个分区，以从最新分区中删除旧数据。
1.将此旧数据与前一个分区合并。否则，它将把这一天的数据留在自己的分区中，最好考虑每天进行自动间隔分区。
下面是针对此特定表执行这些步骤的过程。如果要将表作为参数传递，则需要调整它并添加名称验证。
表格和初始数据：

create table test_temp (
  somedata varchar2(100),
  receiveddate date
)
partition by range(receiveddate) (
  partition p_2022 values less than(date '2023-01-01'),
  partition p_2023 values less than(date '2023-02-08'),
  partition p_last_3_month values less than (date '2023-05-08')
);

select *
from test_temp
partition (p_last_3_month)

create procedure p_keep_3_month_part(
 p_date in date default trunc(sysdate)
)
as
  tmp_partition_name constant varchar2(30) := 'TEMP_PARTITION';
  last_partition_expr long;
  last_partition_name varchar2(30);
  prev_partition_name varchar2(30);

  last_partition_date date;

  /*Dynamic statement logging*/
  procedure execute_immediate(l_stmt in varchar2)
    as
    begin
      dbms_output.put_line(l_stmt);
      execute immediate l_stmt;
    end;

  /*Merge partitions into the lower boundary partition name*/
  procedure merge_partitions_to_lower(p_from in varchar2, p_to in varchar2)
    as
    begin
      execute_immediate('alter table test_temp merge partitions ' ||
        p_from || ' to ' || p_to ||
        ' into partition ' || p_to);
      execute_immediate('alter table test_temp rename partition ' ||
        p_to || ' to ' || p_from);
    end;
begin
  /*get last and previous partition info for merge*/
  select
    partition_name,
    lag(partition_name) over(partition by table_name order by partition_position asc)
  
    into
      last_partition_name,
      prev_partition_name
  from user_tab_partitions
  where table_name = 'TEST_TEMP'
  order by partition_position desc
  fetch first row only;

  /*Add new partition for new date*/
  execute_immediate('alter table test_temp add partition ' || tmp_partition_name ||
    ' values less than (date ''' || to_char(p_date, 'yyyy-mm-dd') || ''')');

  /*Extend last partition to this new date*/
  merge_partitions_to_lower(last_partition_name, tmp_partition_name);

  /*Split old data from the last partition*/
  execute_immediate('alter table test_temp split partition ' || last_partition_name ||
    ' at (date ''' || to_char(add_months(p_date, -3), 'yyyy-mm-dd') ||
    ''') into (partition ' || tmp_partition_name || ', partition ' || last_partition_name || ')');

  /*Merge old data with the previous partition*/
  merge_partitions_to_lower(prev_partition_name, tmp_partition_name);
  
end;/

拆分为2023-05-14（截至今天）：

begin
  dbms_output.put_line('');
  p_keep_3_month_part(sysdate + 4);
end;/

检查结果：

select table_name, partition_name, high_value, partition_position
from user_tab_partitions

select *
from test_temp
partition (p_last_3_month)

fiddle

最好使用INTERVAL分区，它使你的生活更容易，例如像这样：

CREATE TABLE server1.test_temp
    PARTITION BY INTERVAL (INTERVAL '3' MONTH)
    (
    PARTITION P_INITIAL VALUES LESS THAN (TIMESTAMP '2008-01-01 00:00:00')
    )