After seeing many articles about types or partitions , benefits of partitions , different operation on partitions , the one question which will surely arise in DBA’s brain is what about my existing table. I am not going to drop my existing partition and create new partitioned table and insert records to those partitioned table.

So here the good news is we can simply convert our non-partitioned table to partitioned one. Yes..! that’s true.

Let’s check it by taking simple example.

Step 1 : I created a simple non-partitioned table.

SQL> CREATE TABLE MOBILE_SALE_DETAIL
(ID NUMBER,
MODEL VARCHAR2(100),
SALE_DATE DATE)
;  2    3    4    5

Table created.

Now I want to convert this table in range-partitioned table.

Step 2 : Let’s see how.

SQL>  ALTER TABLE MOBILE_SALE_DETAIL MODIFY
  2   PARTITION BY RANGE (SALE_DATE) 
 (PARTITION Q1 VALUES LESS THAN (to_date('01-APR-2019','dd-mon-yyyy')),
  PARTITION Q2 VALUES LESS THAN (to_date('01-JUL-2019','dd-mon-yyyy')),
  PARTITION Q3 VALUES LESS THAN (to_date('01-SEP-2019','dd-mon-yyyy')) ) ONLINE;
    3    4    5  

Table altered.

SQL> 

We can see in above output just like normal modification we do in our table , table is altered and partitions are added to table.

Note : I added ONLINE keyword in my alter command , we can convert non-partitioned to partitioned table without using ONLINE keyword. But when you want to allow DML operations while you convert your table you need to give ONLINE keyword at the end of the alter statement.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

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