0

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?

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

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

For the future partition use NUMTODSINTERVAL instead.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.