相关表:
user_tab_partitions
dba_tab_subpartitions
dba_indexes
dba_ind_partitions
dba_ind_subpartitions
问题说明1:
创建分区表,创建索引,然后分割分区表,再修改分区表初始化大小,重建分区索引并修改分区索引初始化大小。
创建表: create table table_name( ... )compress for oltp partition by range(column1) (partition table_name_pmaxvalue values less than(maxvalue)) storage (initial 3M) tablespace "DATA" enable row movement; alter table table_name add constraint pk_table_name primary key(column1) using index local storage(initial 1M) tablespace "DATA" 分割分区: alter table table_name split partition table_name_pmaxvalue at (high_value) into (partition partition_name,partition table_name_pmaxvalue) update indexes; 改变分区表初始值大小: alter table_name move partition partition_name storage (initial 6M) tablespace "DATA" 改变分区表索引初始化大小: alter index pk_table_name rebuild partition partition_name storage (initial 2M) tablespace "DATA"
问题说明2:
创建分区,子分区表,创建索引,然后分割分区表,再修改分区及子分区初始化大小,重建分区,子分区索引并修改分区索引初始化大小。
与问题1不同,因为有子分区,所以不能直接修改分区和子分区初始化大小,于是在分区分割的时候顺便修改分区大小,子分区大小会自动被修改。
有子分区之后,无法在重建索引时修改索引初始化大小。
创建表: create table table_name( ... )compress for oltp partition by range (column1) subpartition by hash (column2) subpartition 2 --每个分区分割两个子分区 (partition table_name_pmaxvalue values less than(maxvalue)) storage (initial 3M) tablespace "DATA" enable row movement; alter table table_name add constraint pk_table_name primary key(column1) using index local storage (initial 1M) tablespace "DATA" 分区分割时顺便修改分区及子分区的初始化大小: alter table table_name split partition table_name_pmaxvalue at (column1 max high value) into (partition partition_name storage (initial 6M),partition table_name_pmaxvalue) update indexes;