1、增加表分区(add partition) 增加表分区适应于所有的分区形式,其语法是alter table tbname add partition ..... 但是,需要注意对于像list,range这种存在范围值的分区,所要增加的分区值必须要大于当前分区中的最大值 (如果当前存在maxvalue或default的分区,add partition会报错,这种情况只能使用split,后面会讲到),hash分区则无此限制。 例如: SQL> create table t_partition_range (id number ,name varchar2(10)) 2 partition by range(id )( 3 partition r_p1 values less than (10), 4 partition r_p2 values less than (20), 5 partition r_p3 values less than (30)) 6 ; Table created SQL> alter table t_partition_range add partition r_p4 values less than (40); Table altered SQL> select * from user_tab_partitions 2 where table_name ='T_PARTITION_RANGE'; TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE ------------------------------ --------- ------------------------------ ------------------ -------------------- T_PARTITION_RANGE NO R_P1 0 10 T_PARTITION_RANGE NO R_P2 0 20 T_PARTITION_RANGE NO R_P3 0 30 T_PARTITION_RANGE NO R_P4 0 40 SQL> alter table t_partition_range add partition r_p5 values less than (maxvalue); Table altered SQL> select * from user_tab_partitions 2 where table_name = 'T_PARTITION_RANGE'; TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE ------------------------------ --------- ------------------------------ ------------------ ------------- T_PARTITION_RANGE NO R_P1 0 10 T_PARTITION_RANGE NO R_P2 0 20 T_PARTITION_RANGE NO R_P3 0 30 T_PARTITION_RANGE NO R_P4 0 40 T_PARTITION_RANGE NO R_P5 0 MAXVALUE //测试删除分区,LOCAL索引会不会失效。 Hash和list的语法与上类似,这里不再举例。 注意: 1、对于hash分区,当你执行add partition操作的时候,oracle会自动选择一个分区, 并重新分配部分记录到新建的分区,这也意味着有可能带来一些IO操作。 2、执行alter table时未指定update indexes子句: 如果是range/list分区,其local索引和global索引不会受影响; 如果是hash分区,新加分区及有数据移动的分区的local索引和global索引会被置为unuseable,需要重新编译。 3、复合分区完全适用上述所述规则。 SQL> create index idx_range_id on t_partition_range (id) local; Index created SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name 2 from user_ind_partitions 3 where index_name = 'IDX_RANGE_ID'; SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name 2 from user_ind_partitions 3 where index_name = 'IDX_RANGE_ID'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME ------------------------------ ------------------------------ --------------------- -------- ------------------------------ IDX_RANGE_ID R_P1 10 USABLE KARL_SPACE IDX_RANGE_ID R_P2 20 USABLE KARL_SPACE IDX_RANGE_ID R_P3 30 USABLE KARL_SPACE IDX_RANGE_ID R_P4 40 USABLE KARL_SPACE IDX_RANGE_ID R_P5 MAXVALUE USABLE KARL_SPACE SQL> alter table t_partition_range drop partition R_P5 ; Table altered SQL> select index_name ,partition_name ,high_value ,status ,tablespace_name 2 from user_ind_partitions 3 where index_name = 'IDX_RANGE_ID'; INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS TABLESPACE_NAME ------------------------------ ------------------------------ ----------------------- -------- ------------------------------ IDX_RANGE_ID R_P1 10 USABLE KARL_SPACE IDX_RANGE_ID R_P2 20 USABLE KARL_SPACE IDX_RANGE_ID R_P3 30 USABLE KARL_SPACE IDX_RANGE_ID R_P4 40 USABLE KARL_SPACE //STATUS 全部为 USABLE,果然是有效的。 继续.. 2、收缩表分区(coalesce partitions) Coalesce partition是个很有意思的分区功能,仅能被应用于hash分区或复合分区的hash子分区,执行之后, 会自动收缩当前的表分区,比如某表当前有5个hash分区,执行alter table tbname coalesce partitions后就变成4个, 再执行一次就变成3个,再执行一次就变2个,再执行一次就...........就报错了:) ,对于已分区的表至少要有一个分区存在的嘛! 例如: SQL> select table_name,partition_name,tablespace_name 2 from user_tab_partitions 3 where table_name = 'T_PART_HASH'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ T_PART_HASH T_HASH_P1 PART01 T_PART_HASH T_HASH_P2 PART02 T_PART_HASH T_HASH_P3 PART03 SQL> alter table T_PART_HASH add partition t_hash_p4 ; Table altered SQL> select table_name,partition_name,tablespace_name 2 from user_tab_partitions 3 where table_name = 'T_PART_HASH'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ T_PART_HASH T_HASH_P1 PART01 T_PART_HASH T_HASH_P2 PART02 T_PART_HASH T_HASH_P3 PART03 T_PART_HASH T_HASH_P4 KARL_SPACE 注意,收缩的只是分区,并不会影响到数据,但是视被收缩分区中数据的多少,收缩表分区也会涉及到IO操作。 另外如果你在执行该语句时没有指定update indexes子句,收缩过程中有数据改动的分区其local索引和glocal索引都会失效,需要重新编译。 //检验一下INDEX失效的情况 SQL> drop index idx_part_hash_id ; Index dropped SQL> create index idx_part_hash_id on t_part_hash (id ) local; Index created SQL> select index_name ,partition_name ,status ,tablespace_name from 2 user_ind_partitions 3 where index_name = 'IDX_PART_HASH_ID'; INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME ------------------------------ ------------------------------ -------- ------------------------------ IDX_PART_HASH_ID T_HASH_P1 USABLE PART01 IDX_PART_HASH_ID T_HASH_P2 USABLE PART02 IDX_PART_HASH_ID T_HASH_P3 USABLE PART03 IDX_PART_HASH_ID T_HASH_P4 USABLE KARL_SPACE //因为刚才添加的分区 t_hash_p4 没有指定TABLEspace_name,SO..t_hash_p4被默认添加到用户的表空间里。 //而此时建的是local index 。则一样在karl_space表空间里。 //并注意此时INDEX 的status 是 USABLE //下面执行收缩表分区操作。 SQL> alter table t_part_hash coalesce partition; Table altered SQL> select table_name,partition_name,tablespace_name 2 from user_tab_partitions 3 where table_name = 'T_PART_HASH'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ T_PART_HASH T_HASH_P1 PART01 T_PART_HASH T_HASH_P2 PART02 T_PART_HASH T_HASH_P3 PART03 SQL> select index_name ,partition_name ,status ,tablespace_name from 2 user_ind_partitions 3 where index_name = 'IDX_PART_HASH_ID'; INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME ------------------------------ ------------------------------ -------- ------------------------------ IDX_PART_HASH_ID T_HASH_P1 USABLE PART01 IDX_PART_HASH_ID T_HASH_P2 UNUSABLE PART02 //发现UNUSABLE IDX_PART_HASH_ID T_HASH_P3 USABLE PART03 SQL> alter index IDX_PART_HASH_ID rebuild partition t_hash_p2; Index altered SQL> select index_name ,partition_name ,status ,tablespace_name from 2 user_ind_partitions 3 where index_name = 'IDX_PART_HASH_ID'; INDEX_NAME PARTITION_NAME STATUS TABLESPACE_NAME ------------------------------ ------------------------------ -------- ------------------------------ IDX_PART_HASH_ID T_HASH_P1 USABLE PART01 IDX_PART_HASH_ID T_HASH_P2 USABLE PART02 //现在状态OK IDX_PART_HASH_ID T_HASH_P3 USABLE PART03 3、删除表分区(drop partition) 删除表分区包含两种操作,分别是: 删除分区:alter table [tbname] drop partition [ptname]; 删除子分区:alter table [tbname] drop subpartition [ptname]; 除hash分区和hash子分区外,其它的分区格式都可以支持这项操作。 例如,删除分区: SQL> select partition_name, table_name ,subpartition_count 2 from user_tab_partitions 3 where table_name ='T_PART_LIST'; PARTITION_NAME TABLE_NAME SUBPARTITION_COUNT ------------------------------ ------------------------------ ------------------ T_LIST_P1 T_PART_LIST 0 T_LIST_P2 T_PART_LIST 0 T_LIST_P3 T_PART_LIST 0 SQL> select * from t_part_list; ID NAME ----------- ---------- 1 asdfad 2 asfda 3 3333 3 afasdf SQL> select * from t_part_list partition(t_list_p3); ID NAME ----------- ---------- 3 3333 3 afasdf SQL> alter table t_part_list drop partition t_list_p3; Table altered SQL> select * from t_part_list; ID NAME ----------- ---------- 1 asdfad 2 asfda select * from t_part_list partition(t_list_p3) //肯定不在!在就有鬼了! ORA-02149: 指定的分区不存在 注意: 1.由于是ddl操作,这种删除也会是非常迅速的,因此如果你确认某个分区的数据都要被删除, 使用drop partition会比delete更加高效。如果你的本意是希望删除掉指定的分区但保留数据,你应该使用merge partition,后面也会讲到。 2.同样,如果你在执行该语句时没有指定update indexes子句,也会导致glocal索引的失效, 至于local索引嘛,删除分区时对应的索引分区会被同时删除,但其它分区的local索引不会受到影响。