情况一:没有指定maxvalue
alter table EQRECIPEITEMHISTORY_1123 add partition p_05 values less than (to_date('/06/01 00:00:00','yyyy/mm/dd hh24:mi:ss'));
情况二:指定maxvalue
alter table EQRECIPEITEMHISTORY_1123 split partition p_max at (to_date('/06/01 00:00:00','yyyy/mm/dd hh24:mi:ss')) into (partition p_05,partition p_max);
--------------------------------------------------------------------------------------------------------------------------------------------------------
更新查询 分区表
alter table EQRECIPEITEMHISTORY_1123 enable row movement;
UPDATE EQRECIPEITEMHISTORY_1123
SET inserttime = TO_DATE ('/07/22 11:22:11', 'yyyy/mm/dd hh24:mi:ss')
where item='Init_Z';
alter table EQRECIPEITEMHISTORY_1123 disable row movement;
alter table EQRECIPEITEMHISTORY_1123 split partition p_max at (to_date('/08/01 00:00:00','yyyy/mm/dd hh24:mi:ss')) into (partition p_07,partition p_max);
select * from EQRECIPEITEMHISTORY_1123 partition (P_07);
默认情况下,oracle的分区表对于分区字段是不允许进行update操作的,如果有对分区字段行进update,就会报错——ORA-14402: 更新分区关键字列将导致分区的更改。這種情況可以通過開啟表的行移動來允許對分區字段的update 操作:
alter table xxx enable row movement;
之後就可以成功update 分區字段,同時因為行的物理移動,導致rowid發生變化,對應列值的索引葉塊會標記為刪除,插入新的葉塊,重定義完分區后,記得關閉行移動。
alter table xxx disable row movement;