Oracle 11g - drop maxvalue partition, keep data

Question

I have some tables with data like this:

 CREATE TABLE "PARTTEST4" 
 ("year" number(4,0) NOT NULL ENABLE)
 PARTITION BY RANGE ("year") 
 (PARTITION "P_OLD" VALUES LESS THAN (2010),
 PARTITION "P_2020" VALUES LESS THAN (2020),
 PARTITION "P_MAX" VALUES LESS THAN (MAXVALUE));
insert into parttest4 values (2005);
insert into parttest4 values (2015);
insert into parttest4 values (2025);

...in very simplified terms. Some of the tables also have some partitioned global/local indexes. I wish to drop the maxvalue partition (to use SET INTERVAL instead) but:

DROP PARTITION --causes data loss. Unacceptable MERGE PARTITION --violates "VALUES LESS THAN (2020)" if I try to merge the P_2020 + P_MAX

What should I do? Split the P_MAX? What precautions should I take?

Thanks

Answer 1

Split above current maximum value, drop the empty MAXVALUE partition, then convert to interval partitioning:

alter table parttest4 split partition p_max at (2030) into (partition p_2030, partition p_max) update global indexes;
alter table parttest4 drop partition p_max;
alter table parttest4 set interval (10);

• Lynx Kepler: Thanks. Any special considerations to the indexes, beyond the UPDATE INDEXES ?