We have a set of 24 tables that take up about 1tb+ data which is why DBA suggested to use partitioning to better handle usage.

The only issue being the partitioning of the existing tables is something they are not advising as data transfer from current to temp table will take a lot of time(dbms_redefinition), which is why they are suggesting to use a 2 table approach where old table will be static and new table will be partitioned, and select will be a union of the 2 tables as required.

I just wanted to explore if there is any other way of partitioning the existing tables without requiring significant down time

  • What is the motivation for partitioning? "better handle usage" is a bit vague. I'm not necessarily saying it's wrong, but just partitioning all by itself may not be what is required.
    – BobC
    Commented Mar 11, 2017 at 2:10
  • to handle cluster weights, to query table having really huge data. @BobC
    – Ajo Koshy
    Commented Mar 14, 2017 at 4:57
  • Still a bit vague. Can you elaborate on what a "cluster weight" is? How does partitioning help querying a huge table? And can you define "huge"; both it terms of number of rows and segment size.
    – BobC
    Commented Mar 14, 2017 at 5:01

3 Answers 3


where old table will be static and new table will be partitioned, and select will be a union of the 2 tables as required

This is really easy to do in an instant without copying anything or requiring any additional space, and you do not need to use UNION or rewrite anything at all, because you can do this with 1 table.

Let's say your table is this:

create table t1 (col1 date, col2 number);

Then you create the partitioned version of this table with 1 partition where you can store all your existing data:

create table t1_exch (col1 date, col2 number)
partition by range (col1)
interval (numtodsinterval(1, 'day'))
  partition p1 values less than (date'2017-03-11')

Then you exchange your table with the partition:

alter table t1_exch exchange partition p1 with table t1;

Finally drop the old table, and rename the new table to use the name of the old table:

drop table t1 purge;
alter table t1_exch rename to t1;

Data inserted after '2017-03-11' will be automatically partitioned and you can leave your old data as it is, or split into partitions if you want.

Partitioning data is easy, the cumbersome part may be dealing with the constraints and indexes that need to be global. You may need to change your PK and Unique constraints (and indexes), as you can not have such indexes locally partitioned without the partitioning key included in them. Also because of dropping the old table and having a new one in its place, you need to sort out the FK constraints pointing to this table.


Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.

You can redefine the table online and it will be accessible for DML. The table will be locked in an exclusive mode for a negligible period of time, where the size of the table doesn't matter.

The downside is you have to have extra space equal to the size of your table(1TB).


Online Conversion of a Nonpartitioned Table to a Partitioned Table

Nonpartitioned tables can be converted to partitioned tables online. Indexes are maintained as part of this operation and can be partitioned as well. The conversion has no impact on the ongoing DML operations.

In Oracle 12c Release 2, you can convert your non-partitioned table to partitioned table using ALTER TABLE statement. But, as per your tagging, you are in 11g R2 and you don't have this option.


  • Redefining Tables Online
  • Oracle 12.2: Online Conversion of a Nonpartitioned Table to a Partitioned Table

  • 4
    • doesnt DBMS_REDEFINITION take a lot of time?
      – Ajo Koshy
      Commented Mar 10, 2017 at 8:44
    • @AjoKoshy there is no escape from time it takes to go from non-partitioned to partitioned, you just cannot do this instantaneously. Well except when you have empty tables. If you don't want to use dbms_redefinition, use 12.2 where you can partition a table while it is being used. It probably uses online redefinition, but you don't have to code for that.
      – Raj
      Commented Mar 10, 2017 at 20:28
    • 12.2 seems to allow online partitioning without requiring redefinition
      – Ajo Koshy
      Commented Mar 14, 2017 at 4:57
    • @AjoKoshy: Yes! You can.
      – atokpas
      Commented Mar 14, 2017 at 5:11
    1. you can add a date column (say sql_date) to that large table using default sysdate. Thus only the new records will store current date.
    2. Create index on sql_date.
    3. Update sql_date batch by batch.
    4. create a partitioned table with some other name.
    5. copy the data to partitioned table using CTAS batch by batch.

    Once the data is 95% copied. Take a down time.

    1. Copy the remaining data and
    2. Rename the tables (swap the names)
    3. Add the grants
    4. After through testing drop the old table

    Only downside to this is extra space equal to the size of your table(1TB).

    • need to do in 1 shot
      – Ajo Koshy
      Commented Mar 10, 2017 at 8:44

    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.