Re: Partition Advice

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Mon, 26 Jul 2004 22:01:08 +0300
Message-ID: <387f01c47342$e937cac0$0a879fd9@porgand>

You get more information when you read the doc I referred to.

The recovered table doesn't have to be partitioned, it has to be a regular table - when you exchange partition, then you only "switch" the physical contents of the partition and the recovered table, thus the partition has to exist (at least in the data dictionary) and has to have the same column structure that the table you're exchanging it with.

So, if you haven't dropped your old partition, you can just do the exchanging of those segments' contents without any splitting or dropping partitions.

If you have global indexes on your partitioned table, you have to use "UPDATE GLOBAL INDEXES" clause with exchange partition command, otherwise global indexes will be marked unusable...

Another approach would be to drop and recreate the partition and insert the recovered data back into it, but I'd recommend the exchange..

Tanel.

Original Message ----- From: "Sanjay Mishra" <smishra_97_at_yahoo.com> To: <oracle-l_at_freelists.org> Sent: Monday, July 26, 2004 9:49 PM Subject: Re: Partition Advice

> Tanel
>
> Quick advice. Do I need to drop the old partition and
> create the new partition using Split partition or the
> method directly will take care of it. I have currently
> new table with no partition. In Original Table, It is
> range partitioned with data and indexes in different
> tablespace so as to make the Drop partition work
> without unusable indexes error as we used to delete
> old partition after a year or so.
>
> Sanjay
> --- Tanel_Põder <tanel.poder.003_at_mail.ee> wrote:
> > You could use:
> >
> > alter table emp exchange partition old_partition
> > with table employee;
> >
> > And then rebuild any indexes on this partition.
> >
> > That way the physical contents of old_partition in
> > emp table table are
> > replaced with contents of employee table (using a
> > data dictionary update, no
> > real data is moved around).
> >
> > If you are sure about validity of data in the
> > employee table, you could add
> > "without validation" clause to the exchange
> > partition syntax, that way the
> > exchange operation itself will be faster. There are
> > some issues though, I
> > recommend you to read Jonathan Lewis'es article on
> > this:
> > http://www.dbazine.com/jlewis17.shtml
> >
> > Tanel.
> >
> > ----- Original Message -----
> > From: "Sanjay Mishra" <smishra_97_at_yahoo.com>
> > To: <oracle-l_at_freelists.org>
> > Sent: Monday, July 26, 2004 9:14 PM
> > Subject: Partition Advice
> >
> >
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jul 26 2004 - 14:01:27 CDT