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
