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> 
SQL> select constraint_name,status,validated
  2  from user_constraints
  3  where table_name in ('PTAB','FTAB');

CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
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 11.2.0.3.

– 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