Oracle partitioning by range

Question

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

You wouldn't want a trigger that clears history, that's not a thing that makes sense-- what event would you trigger on, for example? You could write a procedure that drops the older partitions and schedule that procedure to run every week. If you're dropping old partitions, though, that would imply that you'd want to regularly create new partitions. In which case, assuming a recent Oracle version, you'd want an interval partitioned table not range partitioned table. You could have your job add new partitions/ split existing partitions but interval partitioning is much cleaner — Justin Cave, Sep 01 '21 at 10:24
In addition to Justins' comments, naming a partition CURRENT_WEEK doesn't make sense either. Next week you'll be adding a new partition and the partition called 'CURRENT_WEEK' will have data that is a week old. You cannot have dynamic partition names. Are you sure partitioning is a solution for your business problem ? What are you trying to do that you think table partitions is the solution for ? — Koen Lostrie, Sep 01 '21 at 11:18
I have a table with logs that accumulate very quickly, about one hundred thousand in a week, it makes no sense to store logs for more than two weeks. At the moment, we store information for more than a month, after which we manually delete older records, I would like to divide the table into two sections, this week and the previous one. I understand that this will not work dynamically, but how to automatically create partitions and delete them if they exceed two weeks. Relative to the table above — Limbo1111, Sep 01 '21 at 12:13

score 3 · Accepted Answer · answered Sep 01 '21 at 13:18

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' 
*/

Thank you very much for your help, but will you tell me how to understand which partition to address, especially in the code, and secondly, how best to organize the cleaning of the table, in the form of a procedure? — Limbo1111, Sep 01 '21 at 13:41
For automatic cleanup have a look at https://stackoverflow.com/a/52243021/3027266 or https://stackoverflow.com/a/48089939/3027266 — Wernfried Domscheit, Sep 01 '21 at 14:24

Oracle partitioning by range

1 Answers1