RE: Partition exchange

From: Shamsudeen, Riyaj <>
Date: Wed, 12 Mar 2008 21:45:03 -0500
Message-ID: <6A4102F59ECFA248B81F7D08F0317978027B3A2C@TBDCEXCH01.US.Cingular.Net>


            You are right that exchange partition command should be dictionary updates, but there are few gotchas.

            Is that table regular heap table or any other special tables such as IOT, compressed tables etc?

            Are the table structures exactly same? Can you run this query for those two tables to see if there is any mismatch?  

select a.owner, a.table_name, a.column_name, b.column_name ,

a.data_type, b.data_type , a.data_precision, b.data_precision , a.data_length, b.data_length,

a.nullable, b.nullable

from dba_tab_columns a , dba_tab_columns b

where a.owner=user and a.table_name=upper('T1') and b.owner=user and b.table_name=upper('T2') and


and (

a.data_type != b.data_type or

a.data_precision!=b.data_precision or

a.data_scale != b.data_scale or

a.data_precision != b.data_precision or

a.nullable != b.nullable



            Also, could you turn on 10046 trace files before exchange partition command and send the trace file please?


Riyaj "Re-yas" Shamsudeen
ERP Financials DBA, New AT&T

[] On Behalf Of Ken Naim Sent: Wednesday, March 12, 2008 7:35 PM
Subject: Partition exchange  

I am in the process of moving the partitions of a table from ASSM to a non-assm tablespace using the partition exchange method (create non partitioned table, indexes, constraints etc.). When I do the alter table exchange partition including indexes without validation command it runs for hours, doing a full table scan on all the partitions of the partitioned table which takes many hours. I understood that just the data dictionary is updated, and it should take a few seconds. Primary Wait event is db file scattered read. Can anyone shed some light on this phenomenon. DB version is and is running on Solaris 10.    



Received on Wed Mar 12 2008 - 21:45:03 CDT

Original text of this message