Re: Collapse partitions in 9i - What all options do i have?

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Tue, 24 Jul 2007 12:43:15 +0100 (BST)
Message-ID: <664555.25983.qm@web86901.mail.ukl.yahoo.com>

Ohh Boy did i forget the merge partition statement if you have range partitions

You can try
ALTER TABLE PART MERGE PARTITIONS P1, P2 INTO PARTITION P12 regards
Hrishy

hrishy <hrishys_at_yahoo.co.uk> wrote:

> Hi
>
> In my opinion the only real option you have is
> option
> 2 and 3.
>
> Option 1 is more work out because you need to run it
> 3
> times.
>
> Option 2 is the most attractive one where you do a
>
> create table my_table parallel X nologging
> as
> sleect * from table partition_table p1
> union all the partitions which you wnat to keep
>
> then drop the partition tables or use
>
> The syntax available here
> http://www.psoug.org/reference/partitions.html
>
> in 10g you can do the partition redefination online.
>
>
>
>
> Hi All,
>
> We are in the process of migrating our Enterprise
> Edition 9i db to Standard
> Edition 9i. As we all are aware, Partitioning is not
> enabled in the Standard
> Edition, we would like to collapse all the
> partitions
> in our db before
> migrating it to Standard Edition. The current DB
> (Enterprise Edition) has 45
> tables with partitions with around a million records
> on an average in each
> table. The worst part is there is no primary key on
> these tables. Initially
> i thought an Online Redefinition of the tables will
> fix the thing for me,
> but the absence of Primary Keys makes this
> impossible.
> The data so far i
> have gathered is this:
>
> 1) Online Redefinition is possible - But we do not
> have Primary keys - Can
> we do with ROWIDs? If yes, how?
> 2) ALTER TABLE EXCHANGE PARTITION can be feasible -
> But i am not much aware
> of this option?
> 3) Tried checking if Export/Import will help but i
> dont think this will.
>
> What are the various ways i have to collapse
> partitions in a DB?
>
> Thanks a lot in advance,
> Vikram Singh,
> Consultant DBA,
> Sierra Atlantic S/W Services Ltd.,
> Hyderabad,
> Andhra Pradesh - 509028
> India.
>
>
>
>
>
>

> Yahoo! Answers - Got a question? Someone out there
> knows the answer. Try it
> now.
> http://uk.answers.yahoo.com/
>

One email account is enough. Simplify your life by switching other accounts into Yahoo! Mail. http://uk.docs.yahoo.com/mail/trueswitch_overview

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

Received on Tue Jul 24 2007 - 06:43:15 CDT