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;

This entry was posted in Oracle on by .
Unknown's avatar

About SandeepSingh

Hi, I am working in IT industry with having more than 15 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply