Oracle partitioning by range

I need to split the table into partitions, namely into three partitions according to the EVENT_TIME field, where the first partition is an interval of a week from today, the second last week and the third partition is history, where data that does not pass into the first two partitions are placed. In addition, I need to add a trigger that will clear the history every two weeks.

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)
(
    PARTITION CURRENT_WEEK VALUES LESS THAN (TO_DATE(TO_CHAR(CURRENT_TIMESTAMP), 'dd-MM-yyyy HH24:mi:ss'))
)
ENABLE ROW MOVEMENT;

I know that this is not a valid request, so I am writing, please help

Answer

It sounds like you want to keep a rolling 2-3 weeks’ worth of data. In which case you can use interval partitioning, dropping the oldest partition each week.

Interval partitioning creates a new partition whenever you insert a row with a value greater than the current highest partition boundary.

All you need to define is an initial partition and the time interval. You can choose any value in the past as the boundary for the initial partition.

For example:

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)
  interval ( interval '7' day ) (
  partition p_init values less than ( date'2021-01-04' )
);

insert into events_log_test_partition 
  values ( 1, 'test', 'test', systimestamp - 14, 'test', 'test' );
insert into events_log_test_partition 
  values ( 2, 'test', 'test', systimestamp, 'test', 'test' );
  
select partition_name, high_value 
from   user_tab_partitions
where  table_name = 'EVENTS_LOG_TEST_PARTITION';
/*
PARTITION_NAME    HIGH_VALUE                        
P_INIT            TIMESTAMP' 2021-01-04 00:00:00'    
SYS_P6002         TIMESTAMP' 2021-08-23 00:00:00'    
SYS_P6005         TIMESTAMP' 2021-09-06 00:00:00' 
*/  
select * from events_log_test_partition
  partition for ( date'2021-08-18' );
/*  
ID    METHOD    INPUT    EVENT_TIME                        STATUS    MESSAGE   
    1 test      test     18-AUG-2021 13.09.17.000000000    test      test  
*/
select * from events_log_test_partition
  partition for ( date'2021-09-01' );
/*  
ID    METHOD    INPUT    EVENT_TIME                        STATUS    MESSAGE   
    2 test      test     01-SEP-2021 13.09.17.516073000    test      test       
*/ 
alter table events_log_test_partition
  drop partition for ( date'2021-08-18' );

select partition_name, high_value 
from   user_tab_partitions
where  table_name = 'EVENTS_LOG_TEST_PARTITION';
/*  
PARTITION_NAME   HIGH_VALUE                        
P_INIT           TIMESTAMP' 2021-01-04 00:00:00'    
SYS_P6005        TIMESTAMP' 2021-09-06 00:00:00' 
*/