How can i set partition p2 to store only letest 3 months data in it?

Question

In this create partition part of code block, how can i set partition 2 to store 3 months latest data within the partition p2 only?

CREATE TABLE server1.test_temp
                PARTITION BY RANGE (receiveddate)
                (
                  PARTITION P2_test_temp VALUES LESS THAN (TO_DATE('08-05-23', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
                )
                AS
                SELECT * FROM server1.test;
              

Answer 1

Better use INTERVAL partition, it makes your life much easier, for example like this:

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

Answer 2

The easiest solution would be to specify interval partitioning that will dynamically allocate new partitions of specified size for new dates. Partition pruning works very well with where conditions and even subqueries and alows dynamic partition selection based on conditions.

However if, for some reason, you still need to have single partition for the last three month with dynamic upper boundary, you need to schedule daily job that will adjust existing partitions accordingly. It should:

1. Add new partition for a new date to use its upper boundary as new upper boundary.
2. Merge this new partition with the "old" last partition.
3. Split the last partition at the 3 month before the date to remove old data from the latest partition.
4. Merge this old data with the previous partition. Otherwise it will leave this one-day data in its own partition and it would be better to consider automatic interval partitioning on daily basis instead.

Below is the procedure that does these steps for this particular table. You need to adjust it and add name validations if you want to pass a table as a parameter.

Table and initial data:

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)

SOMEDATA	RECEIVEDDATE
qwe	2023-05-01
asd	2023-02-09

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;/

Split at 2023-05-14 (as of today's date):

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

1 rows affected

dbms_output:

alter table test_temp add partition TEMP_PARTITION values less than (date '2023-05-14')
alter table test_temp merge partitions P_LAST_3_MONTH to TEMP_PARTITION into partition TEMP_PARTITION
alter table test_temp rename partition TEMP_PARTITION to P_LAST_3_MONTH
alter table test_temp split partition P_LAST_3_MONTH at (date '2023-02-14') into (partition TEMP_PARTITION, partition P_LAST_3_MONTH)
alter table test_temp merge partitions P_2023 to TEMP_PARTITION into partition TEMP_PARTITION
alter table test_temp rename partition TEMP_PARTITION to P_2023

Check the result:

select table_name, partition_name, high_value, partition_position
from user_tab_partitions

TABLE_NAME	PARTITION_NAME	HIGH_VALUE	PARTITION_POSITION
TEST_TEMP	P_2022	TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	1
TEST_TEMP	P_2023	TO_DATE(' 2023-02-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	2
TEST_TEMP	P_LAST_3_MONTH	TO_DATE(' 2023-05-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	3

select *
from test_temp
partition (p_last_3_month)

SOMEDATA	RECEIVEDDATE
qwe	2023-05-01

fiddle