Interval partitioning : A great feature of oracle 11g

Table partitioning is very much useful for data warehousing. But one problem we face before 11g is have to create the partition manually and it is cumbersome work. If we forget to create partition, the ETL job will be failed.

From 11g, oracle enhance the range partitioning with the feature of auto partitioning. Now along with range partition you only have to mention the interval of partition and create a base partition. The syntax is as follows -

CREATE TABLE SALES_PART
(TIME_ID NUMBER,
REGION_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006'));

The violet lines are for interval and base partition. In this example, the partitions are created on daily basis. If want to create them on week basis, write INTERVAL (NUMTODSINTERVAL(7,'day')) and INTERVAL (NUMTOYMINTERVAL(1,'month')) for monthly basis.

The automatic created partitions have system generated name and hence not follow naming convention. So to alter partition name issue following sql command -

ALTER TABLE SALES_PART RENAME PARTITION sys_p22 TO p_third