A Brief Review of Interval Partitioning
With the introduction of interval partitioning in Oracle Database 11g, the need to manually add partitions when inserting data outside the defined range was eliminated. This article briefly reviews several important aspects of interval partitioning. Point 1: Requirement for an Initial Range Partition To use range-interval partitioning , at least one range partition must be defined: SQL > create table tbl ( id number, name varchar2( 10 ), date_ DATE ) PARTITION BY RANGE (date_) INTERVAL (NUMTOYMINTERVAL( 1 , ‘ YEAR ’)) ( PARTITION p1 VALUES LESS THAN (TO_DATE(‘ 4 -6 -2001 ’, ‘DD - MM - YYYY’)) ); After new partitions are added automatically, you might decide to drop partition p1 (which is the only range partition): SQL > insert into tbl values ( 1 ,’test’,TO_DATE(‘ 4 -6 -2002 ‘, ‘DD - MM - YYYY’)); 1 row inserted SQL > insert into tbl values ( 1 ,’test’,TO_DATE(‘ 4 -6 -2003 ‘, ‘DD - MM - YYYY’)); 1 ...