(New Table Partitioning Methods Introduced in Oracle Database 11g)

ALTER TABLE table_name SET INTERVAL (interval value);

ALTER TABLE month_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));
ALTER TABLE day_part SET INTERVALnumtodsinterval(1,'day'));

To change an interval-partitioned table back to a range-partitioned table. Use SET INTERVAL () to disable interval partitioning. 

The database converts existing interval partitions to range partitions, using the higher boundaries of created interval partitions as upper boundaries for the range partitions to be created.

You can also use SET INTERVAL(<numtodsinterval expression>) to change the interval of an existing interval-partitioned table. The database converts existing interval partitions to range partitions, and 
then automatically creates partitions of the specified numeric range or datetime interval as needed for data beyond the highest value allowed for the last range partition.

Restrictions on  interval partitioning

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables

• Must use only one partitioning key column and it must be a DATE or NUMBER

• Cannot create domain indexes on interval partitioned tables

• Are not supported at the sub-partition level

This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

In Oracle/PLSQL, the numtodsinterval function converts a number to an INTERVAL DAY TO SECOND literal.

The syntax for the numtodsinterval function is:

numtodsinterval( number, expression )

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.

Applies To: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:
numtodsinterval(150, 'DAY') would return '+000000150'
numtodsinterval(1500, 'HOUR') would return '+000000062'
numtodsinterval(15000, 'MINUTE') would return '+000000010'

In Oracle/PLSQL, the numtoyminterval function converts a number to an INTERVAL YEAR TO MONTH literal.

The syntax for the numtoyminterval function is:


numtoyminterval( number, expression )

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: YEAR or MONTH.

Applies To:
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For example:
numtoyminterval(100000000, 'MONTH') would return '+0083'
numtoyminterval(100000, 'YEAR') would return '+0001'