Oracle 11 and interval partitioning

If you have used Oracle range partitioning for example month based partitions before Oracle 11 then you needed to do each new month partitions yourself. Oracle 11 introduced interval partitions which can automatically create new partitions for desired intervals for example each months .

Here is simple example how to do range partiotion with interval (there is a lot of other paratmeter to use with partitions but this example just shows the interval):
SQL> CREATE TABLE test_data ( test_id number not null, test_name varchar2(32) not null, test_day date not null ) partition by range (test_day) interval (numtoyminterval(1, 'MONTH')) ( partition before_int_1_jan_2012 values less than (to_Date('01-01-2012', 'dd-mm-yyyy')));

Last partition before interval starts to create new partitions is called transition point. After that partition intervals are automatically created. For example if you insert data into test_data table and test_day value points into the next month thats month partition will be done and data is inserted into it.

It is also possible to alter older range partition tables to use interval with alter table clause:
SQL> ALTER TABLE test_data2 SET interval (numtoyminterval(1, 'MONTH'));

Remember that interval creates partitions with system generated names (you can rename these manually if you want). You can check intervals from user_tab_partitions view for example this way:
SQL> SELECT partition_name, high_value FROM USER_TAB_PARTITIONS WHERE table_name='TEST_DATA';

You can also check certain date partition with following clause:
SQL> SELECT * FROM test_data partition for (to_date('01-02-2012', 'dd-mm-yyyy'));


NOTE! Interval partition can be used only with date or number columns. And it can have only single partitioning key column. And it does not support subpartitions.