alter partition with INTERVAL (11g New feature)

INTERVAL - is a new feature introduced in 11g.
Make sure you have compatible parameter set to >= 11.1.

CREATE TABLE san_interval_tab (
  col1 NUMBER,
  col2 VARCHAR2(10),
  col3 VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-OCT-2010','DD-MON-YYYY'))
);

From above there's only one partition created.

insert into san_interval_tab values (1,'a','b', to_date('20-nov-2010','dd-mon-yyyy'));

--ORA-14400: inserted partition key does not map to any partition -- you get this error as there is no partition exists to hold this value and in 10g environment we will create partitions manually for couple of months or years in advance to take care of these errors.

In 11g, we can easily alter this table partition to utilize INTERVAL partition and no need to worry about monitoring for new partitions.

alter table san_interval_tab add partition part_02 values LESS THAN (TO_DATE('01-dec-2010','DD-MON-YYYY')); --> this is how you would handle without INTERVAL option.

In 11g:
alter table san_interval_tab set interval (NUMTOYMINTERVAL(1,'MONTH')); --> this will create partitions automatically.

alter table san_interval_tab set interval (NUMTOYMINTERVAL(3,'MONTH')); --> example showing the interval range is 3 months instead of 1 month.

Use NUMTODSINTERVAL if you want to range by Day wise.

Check the dictionary table for the partitions and its high values assigned to each:
select partition_name, high_value from user_tab_partitions
 where table_name = 'SAN_INTERVAL_TAB';