Partitioning Tables  «Prev 

Exchange Partition in Oracle

Question: What is the syntax for "EXCHANGE partitionname WITH TABLE tablename" in Oracle?
In Oracle Database 12c, the EXCHANGE PARTITION clause is a powerful tool that allows for the efficient movement of data between tables and partitions. This clause essentially redefines a partition to be the specified table, and the table to be the old partition.
To perform this operation, use the following syntax: 
ALTER TABLE table_name
EXCHANGE PARTITION partition_name
WITH TABLE exchange_table_name [INCLUDING INDEXES] [VALIDATION | {WITHOUT VALIDATION}];

Here
  1. table_name: This is the name of your partitioned table.
  2. partition_name: This is the partition within table_name you wish to exchange with exchange_table_name.
  3. exchange_table_name: This is the name of the table with which you want to exchange the partition.
  4. INCLUDING INDEXES: This is an optional clause. When specified, Oracle will attempt to exchange the index partitions or subpartitions associated with table_name and exchange_table_name. If not specified, index partitions or subpartitions are marked as unusable.
  5. VALIDATION | {WITHOUT VALIDATION}: These are optional clauses specifying whether the rows are validated for the partition constraint.
  6. VALIDATION ensures all rows in the exchanged table satisfy the partition constraints. WITHOUT VALIDATION skips this check.

For instance, consider you have a partitioned table SALES and a separate table NEW_SALES. If you wish to exchange partition SALES_Q1 with the NEW_SALES table, the command would be: 
ALTER TABLE SALES
EXCHANGE PARTITION SALES_Q1
WITH TABLE NEW_SALES;

This command, in effect, swaps the SALES_Q1 partition of the SALES table with the NEW_SALES table. Please ensure that the exchange_table_name and the partitions of table_name are structurally identical and contain compatible data to avoid any errors or data inconsistencies. This includes data types, column order, and constraints.
The EXCHANGE PARTITION clause does not invoke any triggers or replication mechanisms on the affected tables, so bear this in mind when using it in a replicated or trigger-intensive environment. As always, consult the official Oracle documentation or Oracle support for more information and assistance.

Exchange Partition in Oracle Syntax

Exchange Partition in Oracle with Syntax 
ALTER TABLE tablename
EXCHANGE partitionname WITH TABLE tablename 
INCLUDING INDEXES
WITH VALIDATION 
EXCEPTIONS INTO tablename

Exchange Partition in Oracle Example

Exchange Partition in Oracle with Syntax 
ALTER TABLE WHOLESALE_ORDER
EXCHANGE LOWEST_PART WITH LOWPART_TABLE
EXCLUDING INDEXES
WITH VALIDATION 
EXCEPTIONS INTO BAD_PARTS


ALTER TABLE WHOLESALE_ORDER
EXCHANGE LOWEST_PART WITH LOWPART_TABLE
EXCEPTIONS INTO BAD_PARTS

Location 1 Name the partition that gets exchanged here. After the exchange, this partition will contain all the data that was in the table.
Location 2 Name the table that will be exchanged. After the exchage is done, this table will contain all the data that was in the partition.
Location 3 Include or exclude indexes that you have created on the partition and the table in the exchange using either INCLUDING INDEXES or EXCLUDING INDEXES. Default is EXCLUIDING INDEXES.
Location 4 You can specify either WITH VALIDATION (the default) or WITHOUT VALIDATION. Validation checks all the incoming rows against the partition range and accepts only the rows that fit.
Location 5 Rejected rows goes into the table listed here. Use when you validate (WITH VALIDATION) if you wish to save the rejected data.
Location 6 This example accomplishes the same thing as the previous example - the default parameters (EXCLUDING INDEXES and WITH VALIDATION) have been omitted.