Interval Partitioning

Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage.
Now in 11g, Oracle introduced new partition type called INTERVAL PARTITIONING
The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point.
Here an example:
1. just INTERVAL PARTITIONING
2. INTERVAL PARTITIONING with option NUMTOYMINTERVAL
1. Create table with partition transition point called p_empty.

check partition:

What happens after insert statement will be executed, I am expecting than one partition for month of June will be created:

Check row inserted:

Check partition created (SYS_P46), exactly it is SYS_P46 that contains all record inserted for June month

Restrictions apply:
– You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
– Interval partitioning is NOT supported for index-organized tables
2. option NUMTOYMINTERVAL(n, ‘interval_unit’)
NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. The argument n can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value. The value for interval_unit specifies the unit of n and must resolve to one of the following string values:
– ‘YEAR’
– ‘MONTH’

Around the scenery:
– Concept of Partitioning Partitioning enables tables and indexes to be subdivided into individual smaller pieces. Each piece of the database object is called a partition. A partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually.
Out of scope:
– Range-Partitioned Tables and Global Indexes
– Hash-Partitioned Tables and Global Indexes
– List-Partitioned Tables
– Reference-Partitioned Tables
– Composite Partitioned Tables