Change month partition to day partition with current data in Oracle 12c

Question

I have sales table and currently sales table have interval partition by month.

I changed partition type to month to day;

ALTER TABLE sales  set interval (NUMTOYMINTERVAL(1, 'DAY'));

However current data still in Month partitions.

How can i change partition type with existing data on table?

Answer 1

The above affects only partitions created in the future.

If you want to change your monthly partitions into daily partitions, you can use:

ALTER TABLE ... SPLIT PARTITION ...

Example:

ALTER TABLE sales SPLIT PARTITION sales_201701 INTO
( PARTITION sales_20170101 VALUES LESS THAN (TO_DATE('02-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170102 VALUES LESS THAN (TO_DATE('03-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170102 VALUES LESS THAN (TO_DATE('03-JAN-2017','dd-MON-yyyy')),
  PARTITION sales_20170103 VALUES LESS THAN (TO_DATE('04-JAN-2017','dd-MON-yyyy')),
...
  PARTITION sales_...);

More examples: https://oracle-base.com/articles/12c/online-split-partition-and-subpartition-12cr2

Answer 2

ALTER TABLE sales  set interval (NUMTODSINTERVAL(1, 'DAY'));

For the future partition use NUMTODSINTERVAL instead.