Home » RDBMS Server » Server Administration » Partitioning an Existing Table
Partitioning an Existing Table [message #230798] Thu, 12 April 2007 21:25 Go to next message
vejaiz
Messages: 3
Registered: April 2007
Location: Mumbai
Junior Member
I have a big table of 150GB size. I would like to partition the table and its indexes. The table contains 2 years history data with date field. Would like to do a range partition on date for every 3 months.
How do i partition an existing table and its indexes? would be helpful if i can get the step by step commands.

Re: Partitioning an Existing Table [message #230819 is a reply to message #230798] Fri, 13 April 2007 00:31 Go to previous messageGo to next message
harshad.gohil
Messages: 157
Registered: April 2007
Location: USA
Senior Member
I think, first try things in UAT then implement on Prod.

ALTER TABLE <>.<> ADD PARTITION QTR1 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY'))

ALTER TABLE <>.<> ADD PARTITION QTR2 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))

and you have to create global and local indexes accordingly.

Regards,
Harsh

Re: Partitioning an Existing Table [message #230829 is a reply to message #230798] Fri, 13 April 2007 01:01 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no command to partition a non-partitioned table.
You have to recreate it.
Here are 2 ways (there are others):
1/ Create table <new partioned table> as select * from <your table>
2/ use dbms_redefinition package.

Regards
Michel
Previous Topic: shared memory error
Next Topic: Problem in Creation of Database
Goto Forum:
  


Current Time: Mon Dec 05 20:56:57 CST 2016

Total time taken to generate the page: 0.11316 seconds