RDBMS version: 11.2.0.4
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'))
(
PARTITION PRE_2016 VALUES LESS THAN (TO_DATE('31-DEC-2015 23:59:59', 'DD-MON-YYYY HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
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