Partitioning Tables  «Prev 

Exchange Partition in Oracle

Exchange Partition in Oracle Syntax

ALTER TABLE tablename
EXCHANGE partitionname WITH TABLE tablename 
INCLUDING INDEXES
WITH VALIDATION 
EXCEPTIONS INTO tablename

Exchange Partition in Oracle Example

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.