Hello,
I would like to know if there is a way to disable/enable a partition
on Oracle 8.1.7i.
Each partition has there own tablespace.
If a partition(history data about 10G) is not used now,we want to
disable the partition then make the tablespace offline and move the
datafile out from the server,and we can move the datafile back later.
We don't want to drop the partition because the partition will be
used later again and import data will cost too much data.

If I only make the tablespace offline,full table access will fail.

Any suggestions?


wy

On Mon, 05 Jul 2004 00:25:36 -0700, wy wrote:

>
> If I only make the tablespace offline,full table access will fail.
>
> Any suggestions?

Exchange partitions.

alter table eggs exchange partition omlet with table chicken;

If table chicken was empty before, it will contain all the data
from partition omlet. Parition omlet will be emptied, much to the
delight of this forum.

--
Well-behaved women seldom make history

You could exchange the partition with an
empty table. Then leave the (now-empty)
partition in place while you take the (now
large) table offline.

When you want the large data set back in
place you bring it on-line and exchange it
back in.

There are a couple of side-effects with
this that you need to watch out for:
It's probably only viable if you have no global indexes.

Exchange is very expensive if you have PKs and UKs in
place and validated when you exchange

Have a string of empty partitions in the past might
"confuse" the optimizer.


--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"wy" <wy@fudan.edu> wrote in message
news:e8d0244c.0407042325.256c22c0@posting.google.c om...
> Hello,
> I would like to know if there is a way to disable/enable a partition
> on Oracle 8.1.7i.
> Each partition has there own tablespace.
> If a partition(history data about 10G) is not used now,we want to
> disable the partition then make the tablespace offline and move the
> datafile out from the server,and we can move the datafile back later.
> We don't want to drop the partition because the partition will be
> used later again and import data will cost too much data.
>
> If I only make the tablespace offline,full table access will fail.
>
> Any suggestions?
>
>
> wy