Exchanging a Partition of an Interval Partitioned Table

You can exchange interval partitions in an interval-partitioned table. However, you must ensure that the interval partition has been created before you can exchange the partition. You can let the database create the partition by locking the interval partition.

The following example shows a partition exchange for the interval_sales table, interval-partitioned using monthly partitions as of January 1, 2004. This example shows how to add data for June 2007 to the table using partition exchange load. Assume there are only local indexes on the interval_sales table, and equivalent indexes have been created on the interval_sales_june_2007 table.

LOCK TABLE interval_sales
  PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))
  IN SHARE MODE;

ALTER TABLE interval_sales
  EXCHANGE PARTITION FOR (TO_DATE('01-JUN-2007','dd-MON-yyyy'))
  WITH TABLE interval_sales_jun_2007
  INCLUDING INDEXES;

Note the use of the FOR syntax to identify a partition that was system-generated. You can determine the partition name by querying the *_TAB_PARTITIONS data dictionary view to display the system-generated partition name.