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

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

Convert Non-Partition to Partition table online

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.