Oracle

Oracle 11g - 刪除最大值分區,保留數據

  • August 17, 2015

我有一些包含這樣數據的表:

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

……用非常簡單的術語。一些表也有一些分區的全域/本地索引。我希望刪除 maxvalue 分區(改為使用 SET INTERVAL)但是:

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

我該怎麼辦?拆分 P_MAX?我應該採取哪些預防措施?

謝謝

在目前最大值之上拆分,刪除空的 MAXVALUE 分區,然後轉換為間隔分區:

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

引用自:https://dba.stackexchange.com/questions/107541