Range partitioning is a great way to manage historical data in oracle database, data to be inserted in a partitioned table should have the specific partition exists beforehand otherwise the insert operation will fail. How to create partition automatically when the data added to the table?

Interval partitioning is used to implement this feature which is an enhancement to the range partitioning. The example below shows how to create a table with interval partition on each day.

 

CREATE TABLE INTERVAL_PART
(
ID VARCHAR2 (50 BYTE) NOT NULL,
CREATED_DATE DATE DEFAULT SYSDATE
)
TABLESPACE USERS
PARTITION BY RANGE (CREATED_DATE)
INTERVAL ( NUMTODSINTERVAL (1, ‘DAY’) )
( PARTITION VALUES LESS THAN (TO_DATE (‘ 2020-10-02 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)));

 

The table is created with a single partition with 2020-10-02 00:00:00 as high value which acts as transition point, database creates partitions automatically when any data inserted beyond this point. So when you going to insert data above 2020-10-02 00:00:00 it will create a new partition.

You could create a month interval partition table by NUMTOYMINTERVAL (1, ‘MONTH’) function.