Re: Partition exchange

From: Tim Gorman <>
Date: Fri, 14 Mar 2008 20:00:52 -0600
Message-ID: <>


EXCHANGE PARTITION simply re-identifies the segment.  What was a
standalone table is now a partition; what was a partition is now a
standalone table.  So, if you have a partition residing in tablespace
TS_A and a standalone table residing in tablespace TS_B, then after the
EXCHANGE PARTITION you will have a partition residing in TS_B and a
standalone table residing in TS_A.  Nothing moved -- Oracle just
changed the name and type of the segments...

Don't take my word for it -- just try it and see for yourself.... ;-)

Landrum Darrell wrote:
"Exchange partition never moves the data"?
  If you exchange partition from a partitioned table in one tablespace to a table in a different tablespace, the  data has to be moved and that would create reads on the source table.

Darrell Landrum

On Mar 14, 2008, at 8:01 PM, Tim Gorman <> wrote:

This is not true.  Exchange partition *never* moves data in the table -- for this or any other reason.  The "db file scattered read" wait-event is a read, not a write, so something is being scanned for some reason...

Darrell Landrum wrote:
Hey Ken,

When doing the partition exchange to a different tablespace, all of the data has to be moved.  Data dictionary updates "only" would only apply if keeping the segments in the same tablespace.


On Mar 12, 2008, at 7:35 PM, Ken Naim wrote:

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 Fri Mar 14 2008 - 21:00:52 CDT

Original text of this message