Re: Partition Exchange Newbie Question

From: Mihajlo Tekic <mihajlo.tekic_at_gmail.com>
Date: Tue, 16 Jul 2013 12:38:48 -0500
Message-ID: <CAGWRspa2qQR-ZS8qqNsSPG-NK7Qba=WoGNoRRP+zkpMGM+Tawg_at_mail.gmail.com>

If I understand correctly you are planning to exchange a partition with a global temporary table.I'm afraid such an attempt would fail with "ORA-14132: table cannot be used in EXCHANGE" SQL> host oerr ora 14132
14132, 00000, "table cannot be used in EXCHANGE" // *Cause: An attempt was made to issue an ALTER TABLE EXCHANGE PARTITION | // SUBPARTITION command, but the non-partitioned table cannot be used

//          in the EXCHANGE because one or more of the following apply:
//          - it is a typed table
//          - it is a temporary table <===============
//          - it contains ADT columns
//          - it contains nested-table columns
//          - it contains REF columns
//          - it contains array columns
//          - it is an index-organized table
//          - it contains LOB columns
//          - it is a nested table
//          - it is created with row dependency and the partitioned table
is not
//          - it is created without row dependency and the partitioned

table is
// *Action: Make sure the non-partitioned table does not violate any // of the above restrictions for the ALTER TABLE EXCHANGE PARTITION |
// SUBPARTITION command.

~Mihajlo

On Tue, Jul 16, 2013 at 11:37 AM, Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> I want to test a partition exchange as an alternative method of doing some
> work in the database.
>
>
> I was thinking about loading a global temporary table with the data and
> then doing the exchange however I'm curious about the following:
>
>
>
> Let's say that Primary Partitioned Table is like this:
>
>
>
> Table_Name, Partition_Name, Values
>
> TABLEA......Values1.........Values < 10000
>
> TABLEA......Values2.........Values < 20000
>
> TABLEA......Values3.........Values < 30000
>
>
>
> Now, if I load my TEMP table with **all** values, can I do a partition
> exchange and have the values automatically go into the correct partitions
> of the main partitioned table
>
> **OR**
>
> Do we have to load the temp table with each set of values and do the
> partition exchange one set of values at a time?
>
>
>
> I hope that question is clear enough, if not I can try to put together a
> more detailed example of what I'm trying to ask.
>
>
>
> Regards,
>
>
>
> *Chris D. Taylor*
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Tue Jul 16 2013 - 19:38:48 CEST