About

Partition Exchange permits to exchange partition between tables.

After the EXCHANGE PARTITION command is executed, the partition is empty and you can drop it

The data does not physically move from the partition to the new table. This exchange partition statement merely updates the data dictionary to reset a pointer from the partition to the table and vice versa. Because there is no physical movement of data, this exchange does not generate redo and undo, making it faster and far less likely to impact performance than traditional data-movement approaches such as INSERT.

Articles Related

Example

• Create an interim table that is almost identical in structure to your original table, except that it is not partitioned:

create table trans_y05q1 as
select * from trans
where 1=2;

This creates an empty unpartitioned table TRANS_Y05Q1, whose structure is identical to that of TRANS. The 1=2 clause returns false, so no rows of the TRANS table are transferred.

• Exchange the contents of the partition with this new table:

alter table trans 
   EXCHANGE partition y05q1 
   with table trans_y05q1;

This operation makes the data inside the Y05Q1 partition appear inside the TRANS_Y05Q1 table and empties the partition.

• Drop the partition

After the EXCHANGE PARTITION command is executed, the partition is empty and you can drop it.

• Transport the interim table. After the table is created, you can transport it out of the database and archive it for future use.