Interval partitioning

Samples
CREATE TABLE SALES (   PRODUCT_NAME        VARCHAR2(50),		TRANSACTION_DATE    DATE,		SALE_PRICE          NUMBER(10,2),    AMOUNT_SOLD         NUMBER ) PARTITION BY RANGE (TRANSACTION_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (   PARTITION P1 values less than (TO_DATE('03-01-2011','MM-DD-YYYY')) );
 * Create a RANGE-partitioned table with one partition.

Table created CREATE TABLE SALES (   PRODUCT_NAME        VARCHAR2(50),		TRANSACTION_DATE    DATE,		SALE_PRICE          NUMBER(10,2),    AMOUNT_SOLD         NUMBER ) PARTITION BY RANGE (TRANSACTION_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) STORE IN (tbs1, tbs2, tbs3, tbs4) (   PARTITION P1 values less than (TO_DATE('03-01-2011','MM-DD-YYYY')) );
 * Table can be created on multiple tablespaces if needed.

Table created

SQL> column TABLE_NAME format A10 SQL> column PARTITION_NAME format A8
 * Verify the partition creation.

SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions WHERE table_name='SALES';

TABLE_NAME PARTITIO HIGH_VALUE -- SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SQL> insert into SALES values ('XOOM','01-FEB-11', 600, 30000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions  2  WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE --  SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
 * Insert a value within the created partition -> No new partition created.

SQL> insert into SALES values ('DROID','15-MAR-11', 20, 50000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions 2 WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE -- SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES      SYS_P81  TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SQL> insert into SALES values ('DROID2','15-JUL-11', 30, 70000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions 2 WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE -- SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES      SYS_P81  TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES     SYS_P82  TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SQL> insert into SALES values ('DROID PRO','15-JUN-11', 300, 65000); 1 row inserted SQL> SELECT table_name, partition_name, high_value FROM user_tab_partitions  2  WHERE table_name='SALES'; TABLE_NAME PARTITIO HIGH_VALUE --  SALES      P1       TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES     SYS_P81  TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES      SYS_P83  TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA' SALES     SYS_P82  TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA'
 * Insert a value higher than the threshold of the initial partition. Expected a new partition with new one-month range.
 * Insert a value couple months in advance. New partition is created. However, there is a gap still UN-MATERIALIZED.
 * Insert a value in between that un-materialized gap.

Can I change the interval after partitions are created already?
-- To disable the INTERVAL partition SQL> alter table SALES set interval ; Table altered. -- To enable the INTERVAL partition SQL> alter table SALES set interval (NUMTOYMINTERVAL(1,'MONTH')); Table altered.

-- To switch it back to the range partition SQl> alter table SALES set interval;

Table altered.

SQL> SELECT table_name, INTERVAL FROM user_part_tables where table_name = 'SALES';
 * Check whether or not the table is using the INTERVAL partition.

TABLE_NAME         INTERVAL ---                                             SALES               NUMTOYMINTERVAL(1,'MONTH')