Interval partitioning in oracle 11g

Partitioning is simple terms means to divide a big table/index into smaller parts .

Why do we need partitioning?
Ofcourse the question comes to mind that why do we need partitioning or what is the use of partitioning.
Well partitioning has 3 main advantages->
1 Performance improvement by using partitioning  pruning and partition wise joins
2 Mangeability- operations van be performed on per partition basis rather than on whole table
3 Availability- A single partition can be dropped/truncated, different partitions can be stored in different tablespaces, etc

Type of partitioning
1 Range partitions
2 List partitions
3 Hash partitions
4 Composite partitions

What is Interval partitioning ?
Interval partitioning  basically extends range partitions. Traditionally in range partitions, the partitions for future had to be added manually using
alter table add partition command 

otherwise oracle would give following error in insert operation
ORA-14400: inserted partition key does not map to any partition

In Interval partitioning, all the future partitions would be automatically added by oracle on demand  which means that when we fire an insert statement for lets say a  date Aug2010 and when oracle finds that this partition is not available in the table, then it would automatically add this particular partition in the table and the  insert statement would execute succesfully!'

create table  tab_int_part (   start_date  DATE,   store_id  NUMBER )
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION tab_int_part_p1 VALUES LESS THAN (TO_DATE('1-7-2010', 'DD-MM-YYYY')) );

However, there are some restrictions also of interval partitioning-
1 It can only have single partitioning key which can be either number or date datatype
2 It does not support IOT
3 Domain index are not supported these tables.
4 Also, we can create only single level interval partitioned tables and  composite partitioned tables can be only of -interval range, interval list and interval hash partitions.

How to convert range partitioned table to Interval partitioning
ALTER TABLE SET INTERVAL (interval value);
for monthly partitions-
ALTER TABLE table_month_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));
for daily partitions-
ALTER TABLE table_day_part SET INTERVAL (numtodsinterval(1,'day'));


How to convert Interval partition to Range partition

Use "SET INTERVAL ()" to  convert interval partitioning back to range partitioning.
ALTER TABLE table_day_part SET INTERVAL ();


Also, remember that in oracle 11g, there can be a maximum of 1024k-1=1048575 partitions or sub partitions in a table or an index. Also, when using interval partitioning, oracle counts not only the physical partitions, but it actually counts the partitions from the lowest value created to the highest value created because  it assumes  that in between partitions might be created anytime!!