分区维护操作:
移动分区:
通常是移动到不同的表空间。Move
Alter table sales_hash move partition p2 tablespace users;
(单独移动表也可以,达到整理碎片的效果
Alter table t move tablespace users;)
添加分区:
Alter table t add partition p3 values less than……
只能在已经分区表的最后一个分区之后添加,并且最后一个分区使用特定健值定义,不能是maxvalue.
如果想在中间或开始部分,或者maxvalue后,添加分区,使用split 分裂已有分区。
拆分分区:
ALTER TABLE SALES_RANGE SPLIT PARTITION sales_2000
at (TO_DATE('01/05/2001','DD/MM/YYYY'))
INTO ( PARTITION sales_2000_1, PARTITION sales_2000_2);
相当于:PARTITION sales_2000_1 values less than(TO_DATE('01/05/2001','DD/MM/YYYY'))
删除分区:
Alter table t drop partition p3;
Alter table t truncate partition p3;
结合分区:
只是对于hash partition, 用来合并并减少一个partition
set line 150
Select segment_name,partition_name from dba_segments
where segment_name=upper(’sales_hash’);
alter table sales_hash coalesce partition;
合并分区:
合并相邻的分区
ALTER TABLE four_seasons
MERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;
交换表分区:
CREATE TABLE sales_range_temp
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE);
insert into sales_range_temp values(11,11,11,sysdate);
select * from sales_range partition(sales_2000_2);
alter table sales_range exchange partition sales_2000 with table sales_range_temp;