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've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply