You can exchange partitions in a reference-partitioned table, but you must ensure that the data that you reference is available in the respective partition in the parent table.
Example 4-28 shows a partition exchange load scenario for the range-partitioned
orders table, and the reference partitioned
order_items table. The data in the
order_items_dec_2006 table only contains order item data for orders with an
order_date in December 2006.
You must use the
UPDATE GLOBAL INDEXES or
UPDATE INDEXES on the exchange partition of the parent table in order for the primary key index to remain usable. Note also that you must create or enable the foreign key constraint on the
order_items_dec_2006 table in order for the partition exchange on the reference-partitioned table to succeed.
Example 4-28 Exchanging a partition for a reference-partitioned table
ALTER TABLE orders EXCHANGE PARTITION p_2006_dec WITH TABLE orders_dec_2006 UPDATE GLOBAL INDEXES; ALTER TABLE order_items_dec_2006 ADD CONSTRAINT order_items_dec_2006_fk FOREIGN KEY (order_id) REFERENCES orders(order_id) ; ALTER TABLE order_items EXCHANGE PARTITION p_2006_dec WITH TABLE order_items_dec_2006;