How do I alter my existing table to create a range partition in Oracle
If you are using Oracle 12c Release 2
you could use single ALTER
to convert non-partitioned table to partitioned one (this is one way trip):
CREATE TABLE my_tab ( a NUMBER(38,0), b NUMBER(38,0));
ALTER TABLE MY_TAB MODIFY PARTITION BY RANGE (a) INTERVAL (1000) (
PARTITION p1 VALUES LESS THAN (1000)) ONLINE;
You could convert indexes too, adding:
update indexes (index_name [local/global]);
db<>fiddle demo
Beacuse your table non-partitioned you have two options:
- Export data, drop table, create new patitioned table, import data.
- Use split then exchange partition method. https://oracle-base.com/articles/misc/partitioning-an-existing-table-using-exchange-partition
Also, if you want new partition per month read about SET INTERVAL. For example:
CREATE TABLE tst
(col_date DATE)
PARTITION BY RANGE (col_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION col_date_min VALUES LESS THAN (TO_DATE('2010-01-01', 'YYYY-MM-DD')));