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

oracleoracle-12c

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?

Best Answer

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

Related Question