Re: Partition exchange

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_at_evdbt.com> 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
>>
> -- http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l