I sent this earlier to Tim, but somehow did not include the list. Tim was correct ... the data doesn't move.
On Mar 14, 2008, at 9:10 PM, Darrell Landrum wrote:
I stand (well, I sit) corrected. Tim, you are right. As I was typing a reply about it having to move the data, I started having doubts about my recollection and wanted to test it out. The result is that the operation still leaves the data but points the definition of the target table to the tablespace of the source table.On Fri, Mar 14, 2008 at 8:01 PM, Tim Gorman <tim@xxxxxxxxx> 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.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
