Home » SQL & PL/SQL » SQL & PL/SQL » Converting non-partitioned table into partitioned table
Converting non-partitioned table into partitioned table [message #350414] Thu, 25 September 2008 00:15 Go to next message
sameek1211
Messages: 30
Registered: October 2007
Location: India
Member
Hi,
I have 34 Gb non-partitioned table and want to conver it into partitioned table. Can anyone give my steps to do so

Thanx in advance
Re: Converting non-partitioned table into partitioned table [message #350419 is a reply to message #350414] Thu, 25 September 2008 00:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
No way to do it directly. One way can be
- rename the existing table to a new name
- create partitioned data from existing table:
CREATE TABLE <table_name>
PARTITION BY <desired_type> ( <list_of_required_partitions> )
AS SELECT <column_list> FROM <renamed_table_name>

- check the partitioned table content and drop the old table
Re: Converting non-partitioned table into partitioned table [message #350430 is a reply to message #350414] Thu, 25 September 2008 00:59 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And slightly different Option .

Partitioning Existing Table

Thumbs Up
Rajuvan.
Re: Converting non-partitioned table into partitioned table [message #350433 is a reply to message #350419] Thu, 25 September 2008 01:03 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
A modified way:

- rename the existing table to a new name
- create empty partitioned table <part_tab>
- create "small" unpartitioned tables with all rows for one partition <small_tab_i>

- exchange the empty partition with the small table
alter table <part_tab> exchange partition <pi> with table <small_tab_i>;

...

But tested this only for small tables, haven't experiences with such big ones.

Re: Converting non-partitioned table into partitioned table [message #350443 is a reply to message #350414] Thu, 25 September 2008 01:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another one: use DBMS_REDEFINITION.

Regards
Michel
Previous Topic: How to change unconditional select order result (merged)
Next Topic: reg:trigger requirement
Goto Forum:
  


Current Time: Sun Dec 04 12:46:29 CST 2016

Total time taken to generate the page: 0.06482 seconds