However, exchanging a partition of an interval-partitioned table that has not been materialized in the data dictionary, meaning to have an explicit entry in the data dictionary beyond the interval definition, you must manually materialize the partition using the
To change the interval for future partitions, use the
SET INTERVAL clause of the
ALTER TABLE statement. This clause changes the interval for partitions beyond the current highest boundary of all materialized interval partitions. All future partitions of an interval partitioned table are pre-defined through the
INTERVAL clause. As a side effect, an interval-partitioned table does not have the notation of
You also use the
SET INTERVAL clause to migrate an existing range partitioned or range-* composite partitioned table into an interval or interval-* partitioned table. To disable the creation of future interval partitions, and effectively revert to a range-partitioned table, use an empty value in the
SET INTERVAL clause. Created interval partitions are transformed into range partitions with their current high values.
To increase the interval for date ranges, you must ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'); ORA-14767: Cannot specify this interval with existing high bounds
You must create another daily partition with a high bound of February 1, 2007 to successfully change to a monthly interval:
LOCK TABLE transactions PARTITION FOR(TO_DATE('31-JAN-2007','dd-MON-yyyy') IN SHARE MODE; ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');
The lower partitions of an interval-partitioned table are range partitions. You can split range partitions to add more partitions in the range portion of the interval-partitioned table.
To disable interval partitioning on the
transactions table, use:
ALTER TABLE transactions SET INTERVAL ();