I have a requirement to snapshot a large amount of data on a daily basis. This data needs to be kept for a period of X days/weeks/months. Users will be able to query this table, but will always select only a single snapshot (i.e. data as of a single date). My idea was to create a table partitioned by date using interval partitioning, insert into that table on a daily basis, then have a clean up process which drops old partitions.
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
create table xxjkparttest(
a number,
pkey date)
partition by range(pkey) interval(numtodsinterval(1,'day')) (partition p0 values less than (date '2019-04-24'));
insert into xxjkparttest(a, pkey)
select round(mod(level,dbms_random.value(1,level))), trunc(sysdate)+mod(level,5) from dual connect by level <= 100;
drop table xxjkparttest;
The problem is, when I run the above, the partitions sequence isn't in date order. Initially I don't be creating the data up-front, but I am wondering if I can guarantee that partitions will be created with names that are in order according to the partition key? What if I need to back-date some data (i.e. re-create a snapshot for several days ago)?
select partition_name, partition_position from all_tab_partitions where table_name='XXJKPARTTEST' order by partition_position;
P0 1
SYS_P13074 2
SYS_P13070 3
SYS_P13071 4
SYS_P13072 5
SYS_P13073 6
- I was hoping I could simply query all_tab_partitions and loop through, dropping partitions, however as the name is system generated, I'm thinking I can't do this. My alternative is to use normal range partitioning and create the partition with a known name (i.e. PART_YYYYMMDD). However - is there a better way of doing this now? This document (https://docs.oracle.com/database/121/VLDBG/GUID-EAFD703C-EFA9-4819-85BD-79F63B761A96.htm#VLDBG1269) seems to imply that it can be done easily; I quote:
You can also implement a rolling window of data using inserts into the partitioned table.
Is that that you can implement it by writing some code to do it for you? I can see I can use the "drop partition for" syntax, but that means I have to work out which partitions exist - if I need to rerun the process, or run it at a different time, that could be a pain - I ideally want to say "drop all partitions for key < trunc(sysdate) - 100" etc.
Anyone able to offer any help and guidance?