6

What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this?

Especially in cases where there are many constraints on the table like foreign keys.

2 Answers 2

5

This document by Tim Hall demonstrates the steps needed. It is not a non-trivial thing to do.

  • Create a Partitioned Destination Table
    • The destination must have the same constraints and indexes defined.
  • EXCHANGE PARTITION
    • Switch segments from the source table to the destination table
  • Drop the old table
  • Rename the new table and its constraints
  • SPLIT PARTITION
    • Split the single large partition into smaller partitions as required
2
  • 1
    Thanks! Is there a reason why oracle doesn't provide a possibility to do this with a simple "alter table partition ..." statement?
    – lewap
    Mar 10, 2011 at 16:57
  • 2
    I suspect Oracle doesn't provide a simpler syntax because people with large, unpartitioned existing tables will frequently want to have very fine-grained control about how they move their data to a partitioned table. Doing an exchange partition and then splitting the partitions can be syntactically straightforward but generate substantial performance problems. Creating a new table with the appropriate partitions and moving the data over can be parallelized and is relatively simple but it generally requires downtime. Using DBMS_REDEFINITION can be done online but may require monitoring. Mar 10, 2011 at 22:25
1

You cannot add a partition to a non-partitioned table as long as the table is not of type PARTITIONED.

You can either create a new partitioned table and add the non-partitioned table as a partition of it.

Another approach is to use dbms_redefinition package, which is very powerful and flexible in use and most steps can be done in background. Generally, it works in 2 phases:

  1. Online operations with creation of partitioned table and prefill in background.
  2. Switch-over to new table. Online, but not recommended under heavy load.

However, it is not that easy to use and you might trap into some pitfalls.

Since Oracle 12.2.0.1, with ALTER TABLE there is a new modify_to_partitioned clause, which allows to convert it into a partitioned table. However, there are some restrictions and you cannot convert it back to non-partitioned with the same method.

There is even an ONLINE clause as well as UPDATE INDEXES clause with option to define Index as Local or Global index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.