(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'