oracle分区范围修改与数据迁移处理

背景

由于对应用上线后流量越来越大，原来的按年自动分区性能跟不上，因此决定改成按月自动分区，同时将原有分区数据重新迁移到新的分区

步骤

• 修改表分区为一个月一个分区

alter table my_table set INTERVAL (NUMTOYMINTERVAL(1, 'month'));

• 找到原分区将原分区数据按月拆分为新的分区，同时重建全局索引

alter table my_table split PARTITION my_table_001 at (TO_DATE('2023-10', 'SYYYY-MM')) into (partition my_table_0001, partition my_table_001) update indexes;
alter table my_table split PARTITION my_table_001 at (TO_DATE('2023-11', 'SYYYY-MM')) into (partition my_table_0002, partition my_table_001) update indexes;
alter table my_table split PARTITION my_table_001 at (TO_DATE('2023-12', 'SYYYY-MM')) into (partition my_table_0003, partition my_table_001) update indexes;
alter table my_table split PARTITION my_table_001 at (TO_DATE('2024-01', 'SYYYY-MM')) into (partition my_table_0004, partition my_table_001) update indexes;

• 执行完成后查看现有分区情况

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'MY_TABLE';

• 删除原有分区，同时重建全局索引

ALTER table my_table DROP PARTITION my_table_001 update indexes;

• 查看全部索引

SELECT * from user_indexes WHERE table_name = 'MY_TABLE';

• 重建主键索引
  上面步骤加了update indexes的话这里可以省略

ALTER index SYS_C0011986 rebuild;

• 重建全局唯一索引
  上面步骤加了update indexes的话这里可以省略

ALTER index uk_xxx rebuild;

• 检查索引状态语句

select table_name, index_name, partitioned, status from user_indexes where table_name='MY_TABLE';

• 插入数据测试即完成