Skip to Main Content

Oracle Database Discussions

Three questions on Partitioning

flying_penguinJan 5 2016 — edited Jan 7 2016

RDBMS version:

OS : RHEL 6.4

Environment type: OLTP


We have a big unpartitioned table having 4 Billion records and it is 3TB in size.

We would like to partition this table so that it can be easily maintained. We prefer partitioning on a daily basis.

We are required by the law to keep the data only for 15 days. So, we will drop these partitions after 15 days.

The existing table uses tablespaceA.

We would like to range partition (with INTERVAL) by CREATED_DATE column. We would like move the existing data to big base partition called PRE_2016 as shown below.

Rougly, it would look like

create table order_dtl


        order_id                 number,

        billable_flg            not null char(1 char)

        complmntry_flg          not null char(1 char)

        order_quantity_luom     not null varchar2(20)




        created_date            timestamp (6)


partition by range (created_date)

interval( numtodsinterval(1,'DAY'))



STORE IN (tablespaceA, tablespaceB, tablespaceC);

Question1. Any idea how long it will take to do Exchange partitiong using Online Redefinition ? Just a rough estimate would do.

Question2. In the existing table, we have data from the years 2013, 2014 and 2015. When creating the above mentioned partitioned table, Is there a way we could split the base partition for years 2013,2014 and 2015 ?

Question3. After the Online redefinition , I have to manually create the constraints and Indexes . Right ?

Any suggestions/reccomendations are welcome

This post has been answered by JohnWatson2 on Jan 7 2016
Jump to Answer
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 4 2016
Added on Jan 5 2016