RE: Partition exchange

Ken

            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

a.column_name=b.column_name

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?

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



________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ken Naim
Sent: Wednesday, March 12, 2008 7:35 PM
To: oracle-l@xxxxxxxxxxxxx
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 10.2.0.3 and is running on Solaris 10.

 

 

Thanks,

Ken

Other related posts: