OraFAQ Forum: Performance Tuning » converting Normal Table to Partitioned Table

Home » RDBMS Server » Performance Tuning » converting Normal Table to Partitioned Table (Oracle 10.2.0.4, SUSE linux 64 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

converting Normal Table to Partitioned Table [message #381636]

Mon, 19 January 2009 01:13

abs_amit
Messages: 59
Registered: January 2006
Location: delhi

Member

Hi,
i want to convert one normal Table (size 160 GB) to Partitioned table. this table has around 16 months data and new table will be having partition for each month.

please suggest what is the best approach to do that, as size is the only concern here. so how to overcome on that ?

I am thinking about below aproaches:

1. Export- import
2. Create new table (partitioned) and insert data
3. ALTER TABLE <> EXCHANGE PARTITION

Thanks and Regards
Amit Yadav

Report message to a moderator

Re: converting Normal Table to Partitioned Table [message #381641 is a reply to message #381636]

Mon, 19 January 2009 01:36

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you have the space, 2. is the most efficient above all if you are using nologging and, if possible, parallel clause.
3. is not possible.
dbms_redefinition is another option if you need to do it without (or with minimal) downtime.

Regards
Michel

Report message to a moderator

Re: converting Normal Table to Partitioned Table [message #381650 is a reply to message #381641]

Mon, 19 January 2009 02:20

abs_amit
Messages: 59
Registered: January 2006
Location: delhi

Member

Thanks Michel,

we have enough space in database. but we can not use "nologgin" because we have DR database. we can also afford downtime.

1. why 3rd option is not possible? (please check the attached file having detail about the same)

2. So out of possible four approaches, which one is the best (fastest and safest) ?

3. what would you suggest?

Regards
Amit

Attachment: TIP05.txt
(Size: 1.27KB, Downloaded 1333 times)

[Updated on: Mon, 19 January 2009 02:27]

Report message to a moderator

Re: converting Normal Table to Partitioned Table [message #381657 is a reply to message #381650]

Mon, 19 January 2009 02:43

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1. Third is not possible because you can exchange one table with one partition not one table with multiple partitions.

2. CTAS (Create Table As Select)

3. CTAS

Regards
Michel

Report message to a moderator

Previous Topic:	Who is faster Cursor or Recored
Next Topic:	Concurrency and row cache lock problem in oracle10.2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 01:36:39 CST 2024