Home » Convert non-partition to partition table online in Oracle 12c

Convert non-partition to partition table online in Oracle 12c

by tuanlp

 In Oracle 12cR2, we can convert non partitioned table to partitioned online using alter table command.

First identified the column on which we will make non-partition table to partition table.

I have TRAN_ORDER table which having DATE_CAPTURED column on which i will create RANGE partition.

Check the data present in the DATE_CAPUTRED column.

Find minimum and maximum data of column DATE_CAPTURED before start and choose partition according to your choose like quarterly, monthly , weekly etc.

alter table SALES.TRAN_ORDER modify
PARTITION BY RANGE (CREATED)
(partition TRAN_ORDER_2021_Q1 VALUES LESS THAN (TO_DATE('01/04/2021', 'DD/MM/YYYY')),
partition TRAN_ORDER_2021_Q2 VALUES LESS THAN (TO_DATE('01/07/2021', 'DD/MM/YYYY')),
partition TRAN_ORDER_2021_Q3 VALUES LESS THAN (TO_DATE('01/10/2021', 'DD/MM/YYYY')),
partition TRAN_ORDER_2021_Q4 VALUES LESS THAN (TO_DATE('01/01/2022', 'DD/MM/YYYY')),
PARTITION TRAN_ORDER_MAX VALUES LESS THAN (MAXVALUE)) ONLINE;

You may also like