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
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.
Regards,
Darrell
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 10.2.0.3 and is running on Solaris 10.
Thanks,
Ken
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
|
