Oracle automatic partitioning by day

Question

I'm working with an Oracle 11g DB that has an input of 3-5m rows a day. In the future I would like to use partitioning based on the column Timestamp. My goal is to create a new partition for every day, automatically.

I just found ways to create a given range of days i.e. 1-20 but not for a unlimited time (01.01.2014 to mm.dd.yyyy).

Answer 1

Oracle 11g does offer automatic partition creation, you just need to create table with proper syntax like this one:

create table 
pos_data ( 
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3)
) 
PARTITION BY RANGE (start_date) 
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
(  
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY')) 
); 

Here, two partitions have been defined and an interval of one month has been specified. If date goes beyond the max date specified in partition then oracle automatically creates new partition.

Similarly you can specify partition for day range and oracle will take care of rest.

Answer 2

For daily ranges you can do it like this:

create table ...
...
interval(numtodsinterval(1, 'DAY'))
(
  partition log_data_p1 values less than (to_date('22-04-2015', 'DD-MM-YYYY')),
  partition log_data_p2 values less than (to_date('23-04-2015', 'DD-MM-YYYY'))
);

Important to use numtodsinterval instead of numtoyminterval