Home » RDBMS Server » Performance Tuning » converting Normal Table to Partitioned Table (Oracle 10.2.0.4, SUSE linux 64 bit)
icon5.gif  converting Normal Table to Partitioned Table [message #381636] Mon, 19 January 2009 01:13 Go to next message
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
Re: converting Normal Table to Partitioned Table [message #381641 is a reply to message #381636] Mon, 19 January 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: converting Normal Table to Partitioned Table [message #381650 is a reply to message #381641] Mon, 19 January 2009 02:20 Go to previous messageGo to next message
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 144 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 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Who is faster Cursor or Recored
Next Topic: Concurrency and row cache lock problem in oracle10.2
Goto Forum:
  


Current Time: Wed Dec 07 20:34:20 CST 2016

Total time taken to generate the page: 0.09335 seconds