How to add partitions to an existing table in Oracle database

When the data in the table becomes bigger, we’ll need partitioning in order to make our SQL command faster. But the thing is we already have data in that table. These queries will help you do that with just one execution step.

--create new partitioned table from old table
CREATE TABLE TBL_REQUEST_TEMP
 PARTITION BY RANGE(REQUEST_TIME)(
     PARTITION P201906 VALUES LESS THAN (TO_DATE('20190701','YYYYMMDD')),
     PARTITION P201907 VALUES LESS THAN (TO_DATE('20190801','YYYYMMDD')),
     PARTITION P201908 VALUES LESS THAN (TO_DATE('20190901','YYYYMMDD')),
     PARTITION P201909 VALUES LESS THAN (TO_DATE('20191001','YYYYMMDD')),
     PARTITION P201910 VALUES LESS THAN (TO_DATE('20191101','YYYYMMDD')),
     PARTITION P201911 VALUES LESS THAN (TO_DATE('20191201','YYYYMMDD')),
     PARTITION P201912 VALUES LESS THAN (TO_DATE('20200101','YYYYMMDD'))
 ) AS SELECT * FROM TBL_REQUEST;

--rename old existing table to backup
 ALTER TABLE TBL_REQUEST RENAME TO TBL_REQUEST_BACKUP;

--rename new temp table to old primary name
 ALTER TABLE TBL_REQUEST_TEMP RENAME TO TBL_REQUEST;

I’ll explain those SQL now.

First of all, I will create new partitioned table from old existing table with CTAS (CREATE TABLE AS SELECT) command. This is an extreme popular SQL command but with partition declared.

After we have new partitioned table created with a suffix TEMP, we need to rename the old table with suffix BACKUP.

Finally, we rename the new TEMP table to proper name as we wished:

Note:

This CTAS command not create any index or trigger for you. So we need to double check newly created table and add missing part if need.