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

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

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:

Online Redefinition is possible - But we do not have Primary keys - Can we do with ROWIDs? If yes, how?
ALTER TABLE EXCHANGE PARTITION can be feasible - But i am not much aware of this option?
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/

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

Received on Tue Jul 24 2007 - 06:24:51 CDT