How to partition an existing Oracle table?

Dear Experts,

We have an Oracle 11.0.2 Database and the tables in it has an average size of approximately 5 GB.

We need to partition these existing tables (interval partitioning)  according to the selected time period using an existing field with DATE type. This field is common in all tables. Some of the tables will be partitioned daily, some of them will be partitioned monthly etc.

How can I convert these un-partitioned tables to interval partitioned tables (range partition + auto-increment) without disturbing data or recreating the tables?

BR,
GurcanKAsked:
Who is Participating?
 
PilouteConnect With a Mentor Commented:
Hi Gurcan,

There's no way to split data in partitions 'without disturbig data or recreating the tables'. In fact you can keep your old tables, but the partitioned ones will necessarily be new tables, using new segments, new rowids, etc...

The "clean" way to partition a non-partitioned table is the package rdbs_redefinition. You have a concrete example here about how to use it :
http://www.oracle-base.com/articles/misc/PartitioningAnExistingTable.php

You can always check the official oracle doc too :
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_redefi.htm#CBBJJAIF

Cheers,
P
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.