Re: Partition exchange

From: Darrell Landrum <>
Date: Wed, 12 Mar 2008 22:53:47 -0500
Message-Id: <>

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: [mailto:oracle-l-
>] On Behalf Of Darrell Landrum
> Sent: Wednesday, March 12, 2008 8:47 PM
> Cc:
> 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
> and is running on Solaris 10.
> Thanks,
> Ken

Received on Wed Mar 12 2008 - 22:53:47 CDT

Original text of this message