Exchange Partition Example

I put together an example of how to do an exchange partition to meet a specific need in our environment.  It might be helpful to others though your situation may vary.  It is a self-contained test script in a zip.

There is documentation in the script but here are the criteria:

-- example of partition exchange with following conditions:
-- Range partitioned table
-- Indexes PK, regular, bitmapped - all local
-- validated FK
-- trigger
-- parallel 8 nologging
-- tables:
-- ptab - partitioned table
-- ftab - table related by FK
-- etab - table to be exchanged in

Here is the actual exchange:

-- exchange etab with ptab partition p1:

alter table ptab 
exchange partition p1 
with table etab
including indexes 
without validation;

One side effect is that the constraints that were ‘VALIDATED’ before are now ‘NOT VALIDATED’:

SQL> -- show constraint status
SQL> select constraint_name,status,validated
  2  from user_constraints
  3  where table_name in ('PTAB','FTAB');

------------------------------ -------- -------------
FTAB_PK                        ENABLED  VALIDATED
PTAB_PK                        ENABLED  NOT VALIDATED
FK_C3                          ENABLED  NOT VALIDATED

I built this script to reorganize a partitioned table so it assumes you just want to copy the rows reorganizing them more efficiently in the blocks.  This was tested on

– Bobby



About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply