Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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
ALTER TABLE sales set interval (NUMTODSINTERVAL(1, 'DAY'));
For the future partition use NUMTODSINTERVAL instead.
