Re: Partition Advice
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Jul 2004 22:01:08 +0300
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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxx> 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@xxxxxxxxx>
> > To: <oracle-l@xxxxxxxxxxxxx>
> > 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
