Oracle Database Table partitioning Exchange a partition
Example
Exchange/convert a partition to a non-partitioned table and vice versa. This facilitates a fast "move" of data between the data segments (opposed to doing something like "insert...select" or "create table...as select") as the operation is DDL (the partition exchange operation is a data dictionary update without moving the actual data) and not DML (large undo/redo overhead).
Most basic examples :
- Convert a non-partitioned table (table "B") to a partition (of table "A") :
Table "A" doesn't contain data in partition "OLD_VALUES" and table "B" contains data
ALTER TABLE "A" EXCHANGE PARTITION "OLD_VALUES" WITH TABLE "B";
Result : data is "moved" from table "B" (contains no data after operation) to partition "OLD_VALUES"
- Convert a partition to a non-partitioned table :
Table "A" contains data in partition "OLD_VALUES" and table "B" doesn't contain data
ALTER TABLE "A" EXCHANGE PARTITION "OLD_VALUES" WITH TABLE "B";
Result : data is "moved" from partition "OLD_VALUES" (contains no data after operation) to table "B"
Note : there is a quite a few additional options, features and restrictions for this operation
Further info can be found on this link ---> "https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1107555" (section "Exchanging Partitions")
