网站建设 招标任务书,黄骅贴吧的最新消息,遵义做企业网站,鞍山建立公司网站的步骤生产库中某些大表的分区异常#xff0c;需要对现有表进行在线操作#xff0c;以添加丢失分区#xff0c;因为是生产库#xff0c;还是谨慎点好#xff0c;今天有空#xff0c;针对addspli生产库中某些大表的分区异常#xff0c;需要对现有表进行在线操作#xff0c;以添…生产库中某些大表的分区异常需要对现有表进行在线操作以添加丢失分区因为是生产库还是谨慎点好今天有空针对addspli生产库中某些大表的分区异常需要对现有表进行在线操作以添加丢失分区因为是生产库还是谨慎点好今天有空针对addsplit分区对globallocal索引的影响进行了测试测试版本为Oracle11.2.0.4过程如下首先创建分区表CREATE TABLE TP1(C1 INT PRIMARY KEY,C2 VARCHAR2(10),C3 CHAR(10))partition by range (c1)(partition p1 values less than(6),partition p2 values less than(11),partition p3 values less than(16),partition maxvalue values less than(maxvalue));但在add partition时遇到了问题报错如下ORA-14074: 分区界限必须调整为高于最后一个分区界限根本原因是存在最后maxvalue分区于是再创建一个不带maxvalue的分区表CREATE TABLE TP2(C1 INT PRIMARY KEY,C2 VARCHAR2(10),C3 CHAR(10))partition by range (c1)(partition p1 values less than(6),partition p2 values less than(11),partition p3 values less than(16));然后创建local索引create index idx_tp2_c2 on tp2(c2) local;因为主键就是个global索引所以不需要另外创建global index我们insert数据insert into tp2 vlaues(1,aaa,aaa);insert into tp2 vlaues(2,aaa,aaa);insert into tp2 vlaues(3,aaa,aaa);insert into tp2 vlaues(4,aaa,aaa);insert into tp2 vlaues(5,aaa,aaa);insert into tp2 vlaues(6,aaa,aaa);insert into tp2 vlaues(7,aaa,aaa);insert into tp2 vlaues(8,aaa,aaa);insert into tp2 vlaues(9,aaa,aaa);insert into tp2 vlaues(10,aaa,aaa);commit;我们add partitionALTER TABLE tp2 add PARTITION p10 values less than(51);add partition成功因为不存在maxvalue分区而且add partition对global和local索引均无影响那么split partitionALTER TABLE tp2 SPLIT PARTITION p2 AT (8) INTO (PARTITION p2, PARTITION p22) ;发现split分区会导致golbal索引失效而local索引正常为split分区命令添加update indexes选项后再split分区对globallocal索引均无影响ALTER TABLE tp2 SPLIT PARTITION p1 AT (3) INTO (PARTITION p1, PARTITION p11) update indexes;以上为测试结果记录于此以便今后其他同学和自己参考。本文永久更新链接地址本文原创发布php中文网转载请注明出处感谢您的尊重
