Oracle 11g – drop maxvalue partition, keep data

alter-tableoracleoracle-11g-r2partitioning

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

Best Answer

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);
Related Question