0

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

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);
1
  • Thanks. Any special considerations to the indexes, beyond the UPDATE INDEXES ? – Lynx Kepler Jul 20 '15 at 18:40

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.