Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: exchange partition in 9.2

RE: exchange partition in 9.2

From: Jacques Kilchoer <>
Date: Mon, 16 Jun 2003 16:09:31 -0700
Message-ID: <>

Did you say alter table exchange partition ... validate or alter table exchange partition ... novalidate? If you say novalidate then Oracle will sort the exchange table to make sure that you are not creating duplicate rows for the PK constraint. There is a Metalink note on that.
Does the table have a PK enforced by a locally partitioned unique index? Any global indexes?
What options did you have on the exchange partition statement?

> -----Original Message-----
> From: Gurelei [mailto:[EMAIL PROTECTED]
> We are having an interesting issue with the exchange
> partition command in our shop. We are running oracle
> 9202 on AIX 5l. The exchange partition command used
> to take about 1 sec (or less) in our old environmen
> (oracle 81 Dynix 4.3.3). Now the same operation takes
> about a minute and creates a ton of IO. an Oracle rep
> said that this has to do something with the way
> exchange partition now treats primary and unique keys.
> Recently however a DBA on my team did 4 exchanges in a
> row between the same two tables. First one took about
> 45s and did a lot of IO. next one took almost no time
> and no IO (and it did move the data), the next one -
> again 45s and IOs, the fourth one - no time again.
> Does any one has any experience with this issue and
> may be can shed some light on it.

Please see the official ORACLE-L FAQ:
Author: Jacques Kilchoer

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Jun 16 2003 - 18:09:31 CDT

Original text of this message