Re: Partition exchange

I could see the partition exchange from partitioned table in tablespace A to heap table in tablespace B taking a long time and I could guess that it would be possible for a full table scan involving all partitions to occur for index maintainance (are any of the indexes global?) or other reason that I'm not thinking of right now. I also agree with you that the partition exchange from heap table in tablespace B to partitioned table in tablespace B should be pretty quick, but there still may be a matter of index maintenance. I've done this exact type of operation for similar reasons, but dropped the indexes first and rebuilt afterward so I'm in part guessing until I can test as well.

On Mar 12, 2008, at 10:20 PM, Ken Naim wrote:

> I could understand it if the full scan was on a partition but all
> 18 partitions are being scanned. Also the partition after the
> exchange is the right tablespace (same one that it was in when it
> was a heap table). So that doesn’t make much sense to me.
>
> Ken
>
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-
> bounce_at_freelists.org] On Behalf Of Darrell Landrum
> Sent: Wednesday, March 12, 2008 8:47 PM
> Cc: oracle-l_at_freelists.org
> Subject: Re: Partition exchange
>
> 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