暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 分区表的管理

分区表的管理

wzf0072 2024-11-02
67

分区表的管理


1.1 增加表分区（add partition）
语法：

SQL> alter table table_name add
partition partition_name values less than (to_date('2014-01-01','yyyy-mm-dd')), ---添加range分区
partition prartition_name values('男') , ---添加list分区
partition partition_max values less than (maxvalue/default), ---添加range、list的边界值分区
partition prartition_name tablespace tablespace_name ---添加hash分区
[update indexes]; ---针对hash分区在添加分区时，表中有全局和本地索引时新加入的分区会失效，加上uodate indexes不会导致分区失效
总结：与hash相关的表分区，如果有分区索引，不管是本地还是全局新加入的分区会失效，需要重建新的索引分区，加上update indexes不需要重建



案例一：range/list分区添加2个新分区（分区无maxvalue/default值。不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_R1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_partitions where table_name='TABLE_R1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME' and INDEX_OWNER='USER';


（4）增加2分区时根据上一个分区的范围来定义



SQL> alter table table_r1 add partition P2018 values less than (to_date('2018-01-01','yyyy-mm-dd')) [update indexes];
SQL> alter table table_r1 add partition P2019 values less than (to_date('2019-01-01','yyyy-mm-dd')) [update indexes];
添加范围分区时只能一个一个分区添加，不能一条alter...add添加2个分区会报错

SQL> select * from user_tab_partitions where table_name='TABLE_R1';


（5）查看索引是否有效（上步增加2个分区时没有加update indexes，普通索引和本地索引都有效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效


案例二：range/list分区添加一个新分区（分区有maxvalue/default值。不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_R1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_partitions where table_name='TABLE_R1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME';


（4）增加1分区时根据上一个分区的范围来定义



错误增加分区的写法：

SQL> alter table table_r1 add partition P2020 values less than (to_date('2019-01-01','yyyy-mm-dd')) [update indexes];
因为有范围分区中有maxvalue分区，所以在添加一个分区时表ora-14074错误，添加的分区的值一定要大于最后一个分区，所以只能拆分p_max分区或者删除后添加



正确增加分区的写法：拆分p_max分区

SQL> alter table table_r1 split partition p_max at (to_date('2020-01-01','YYYY-MM-DD')) into (partition p2020,partition p_max); ---一个分区一次只能拆分为2个分区，不能一条alter...split partition拆分2个以上分区会报错

SQL> select * from user_tab_partitions where table_name='TABLE_R1';


（5）查看索引是否有效（上步对分区进行拆分没有加update indexes，普通索引和本地索引都有效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效




案例三：hash分区添加2个新分区（不加update indexes，本地分区索引、全局分区索引、普通索引都将失效，所以要加update indexes）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_H1';


SQL> select * from user_tab_partitions where table_name='TABLE_H1';


（2）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_H1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_H1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_H1_NAME';


（3）增加2分区

系统自动命名分区名添加：
SQL> alter table table_h1 add partition; ---内部命名的分区名为SYS_P378

自定义分区名添加：
SQL> alter table table_h1 add partition p6 update indexes; ---注：添加hash分区时只能一个一个分区添加，不能一条alter...add添加2个分区会报错

SQL> select * from user_tab_partitions where table_name='TABLE_H1';


（4）查看索引是否有效（上步系统自动命名的分区没有加update indexes，导致普通索引和创建的分区索引失效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_H1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_H1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_H1_NAME'; ---系统自动命名的分区没有加上update indexes，导致分区SYS_P378失效，也导致了p1失效。添加p6分区加上了update indexes索引分区p6可用


（5）对索引进行重建

SQL> alter index I_TABLE_H1_CARDID rebuild partition p1 online;
SQL> alter index I_TABLE_H1_CARDID rebuild partition SYS_P378 online;

SQL> select * from user_ind_partitions where index_name='I_TABLE_H1_CARDID';




案例四：复合分区增加2个分区（range—hash / list—hash。不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_RH1';


SQL> select * from user_tab_partitions where table_name='TABLE_RH1';


SQL> select * from user_tab_subpartitions where table_name='TABLE_RH1';


（2）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_RH1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_RH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_RH1_NAME';
SQL> select index_name,partition_name,status from dba_ind_subpartitions where index_name='TABLE_RH1_NAME';


（3）增加2分区

为复合分区增加分区之前，查看下最后一个分区建表语句，用于增加分区的参考



SQL> alter table table_rh1 add partition P2017 values less than (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) subpartitions 2 [update indexes];
SQL> alter table table_rh1 add partition P2018 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) subpartitions 1 [update indexes];

select * from user_tab_subpartitions where table_name='TABLE_RH1';


（4）查看索引是否有效（上步对分区进行添加没有加update indexes，普通索引和本地索引都有效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_RH1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_RH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_RH1_NAME'; ---拥有子分区的索引需要用user_ind_subpartitions查看索引的状态
SQL> select index_name,partition_name,status from dba_ind_subpartitions where index_name='TABLE_RH1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效






1.2 拆分表分区（split partition，支持范围、列表、间隔分区；不支持hash等。具体参考官方手册。并且一个分区一次只能拆分为2个分区，不能一条alter...split partition拆分2个以上分区会报错）


案例一：范围分区拆分at，将一个分区拆分为2个分区（不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_R1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

select * from user_tab_partitions where table_name='TABLE_R1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME';


（4）将p_max拆分成2分区时根据边界分区的范围来定义





SQL> alter table table_r1 split partition p_max at (to_date('2020-01-01','YYYY-MM-DD')) into (partition p2020,partition p_max) [update indexes]; ---将表table_r1 p_max分区拆分，范围分区at为拆分的值，拆分成p2020、p_max二个分区

SQL> select * from user_tab_partitions where table_name='TABLE_R1'; ---将p_max拆分为2个分区


（5）查看索引是否有效（上步对分区进行添加没有加update indexes，普通索引和本地索引都有效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效




案例二：列表分区拆分values，将一个分区拆分为2个分区（不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_L1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_partitions where table_name='TABLE_L1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_L1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_L1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_L1_NAME';


（4）拆分2分区时根据边界分区的值来定义





SQL> alter table table_l1 split partition p_max values ('中性') into
(partition par_03,partition p_max) [update indexes]; ---将表table_r1 p_max分区拆分，列表分区values为拆分的值，拆分成par_03、p_max二个分区

SQL> select * from user_tab_partitions where table_name='TABLE_L1'; ---将p_max拆分为2个分区


（5）查看索引是否有效（上步对分区进行添加没有加update indexes，普通索引和本地索引都有效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_L1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_L1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_L1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效






1.3 删除表分区（drop partition，支持范围、间隔、列表分区；不支持hash、引用分区等）


一、删除分区语法（不加update indexes全局分区索引、普通索引都将失效，本地分区索引自动维护，所以要加update indexes）：
SQL> alter table table_name drop partition partition_name [update indexes];


eg示例：
SQL> alter table ITPUX_R1 drop partition p2015;
SQL> select index_name,status from dba_indexes where table_name='ITPUX_R1' and owner='ITPUX';


SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='ITPUX_R1' and owner='ITPUX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='ITPUX_R1_NAME' and INDEX_OWNER='ITPUX';
Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效





二、删除子分区语法（不加update indexes全局分区索引、普通索引都将失效，本地分区索引自动维护，所以要加update indexes）：
SQL> alter table table_name drop subpartition partition_name [update indexes];


eg示例：
SQL> alter table ITPUX_RR1 drop subpartition P2014_SUB_P_30;
SQL> select index_name,status from dba_indexes where table_name='ITPUX_RR1' and owner='ITPUX';


SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='ITPUX_RR1' and owner='ITPUX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='ITPUX_RR1_NAME' and INDEX_OWNER='ITPUX';
SQL> select index_name,partition_name,SUBPARTITION_NAME,status from dba_ind_subpartitions where index_name='ITPUX_RR1_NAME' and INDEX_OWNER='ITPUX';




1.4 截断表分区（truncate partition，支持范围、间隔、列表分区；不支持hash、引用分区等）
一、截断分区语法（不加update indexes仅全局分区索引失效，普通索引、本地分区索引自动维护，所以要加update indexes）：
SQL> alter table table_name truncate partition partition_name [drop storage] [update indexes];
###1、drop storage：truncate partiton不是物理删除，行所占用的空间不能重新分配；加上drop storage物理空间被重新分配其他对象可以使用


eg示例：
SQL> alter table ITPUX_L1 truncate partition PAR_02;
SQL> select index_name,status from dba_indexes where table_name='ITPUX_L1' and owner='ITPUX';


SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='ITPUX_L1' and owner='ITPUX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='ITPUX_L1_NAME' and INDEX_OWNER='ITPUX';
Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效





二、截断子分区语法（不加update indexes仅全局分区索引失效，普通索引、本地分区索引自动维护，所以要加update indexes）：
SQL> alter table table_name truncate subpartition partition_name [drop storage] [update indexes];
###1、drop storage：truncate partiton不是物理删除，行所占用的空间不能重新分配；加上drop storage物理空间被重新分配其他对象可以使用


eg示例：
SQL> alter table ITPUX_RL1 truncate subpartition SUB_P2018_MAX;
SQL> select index_name,status from dba_indexes where table_name='ITPUX_RL1' and owner='ITPUX';


SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='ITPUX_RL1' and owner='ITPUX';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='ITPUX_RL1_NAME' and INDEX_OWNER='ITPUX';
SQL> select index_name,partition_name,SUBPARTITION_NAME,status from dba_ind_subpartitions where index_name='ITPUX_RL1_NAME' and INDEX_OWNER='ITPUX';




1.5 收缩表分区（coalesce partition，只对hash分区有效，收缩分区的数据分配到其他分区不会丢失数据）


案例一：收缩hash表分区，将6个分区收缩为5个（不加update indexes，本地分区索引、全局分区索引、普通索引都将失效，所以要加update indexes）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_H1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_partitions where table_name='TABLE_H1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_H1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_H1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_H1_NAME';


（4）收缩hash表分区

SQL> alter table table_h1 coalesce partition [update indexes]; ---执行一次收缩一次，以此类推

SQL> select * from user_tab_partitions where table_name='TABLE_H1';


（5）查看索引是否有效（上步收缩p6分区没有加update indexes，导致普通索引和创建的分区索引失效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_H1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_H1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_H1_NAME'; ---收缩p6分区没有加上update indexes，导致了p2效。Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效


（5）对索引进行重建

SQL> alter index TABLE_H1_NAME rebuild partition p2 online; ---分区索引必须对每个分区重建，不能作为整体重建。如果将分区索引作为整体重建会报：ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_H1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_H1_NAME';




案例二：收缩range/list--hash表分区（不加update indexes，本地分区索引、全局分区索引、普通索引都将失效，所以要加update indexes）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_RH1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_subpartitions where table_name='TABLE_RH1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_RH1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_RH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_RH1_NAME';
SQL> select index_name,partition_name,status from dba_ind_subpartitions where index_name='TABLE_RH1_NAME';


（4）收缩表分区

SQL> alter table table_rh1 modify partition p2017 coalesce subpartition [update indexes]; ---对表的range---hash分区p2017进行hash收缩1次。执行一次收缩一次，以此类推

SQL> select * from user_tab_partitions where table_name='TABLE_RH1';


（4）查看索引是否有效（上步收缩复合p2017分区没加update indexes，导致普通索引和创建的分区索引失效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_RH1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_RH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_RH1_NAME';
SQL> select index_name,partition_name,status,SUBPARTITION_NAME from dba_ind_subpartitions where index_name='TABLE_RH1_NAME'; ---收缩p6分区没有加上update indexes，导致了p2效。Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效


（5）对索引进行重建

SQL> alter index TABLE_RH1_NAME rebuild subpartition SYS_SUBP387 online;
SQL> alter index TABLE_RH1_NAME rebuild subpartition SYS_SUBP389 online; ---分区索引必须对每个分区重建，不能作为整体重建。如果将分区索引作为整体重建会报：ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_RH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_RH1_NAME';
SQL> select index_name,partition_name,status,SUBPARTITION_NAME from dba_ind_subpartitions where index_name='TABLE_RH1_NAME'; ---收缩p6分区没有加上update indexes，导致了p2效。Local索引进行add/drop/split/truncate表的分区时，会自动维护其索引分区不失效






1.6 合并表分区（Merge Partitions，支持范围、间隔、列表分区；不支持hash、引用分区等。只能合并两个相邻的分区）


案例一：合并范围分区（不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_R1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_partitions where table_name='TABLE_R1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_R1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME';


（3）合并2分区时根据上一个分区的范围来定义



SQL> alter table TABLE_r1 merge partitions p2016,p2017 into partition p2017 [update indexes]; ---在合并分区时，into partition的分区名要为下界分区（如：合并p2016,p2017，下界分区名为p2017），不然报：ORA-14275: 不能将下界分区作为结果分区重用

SQL> select * from user_tab_partitions where table_name='TABLE_R1';


（5）查看索引是否有效（上步对分区进合并加没有加update indexes，普通索引和本地索引都有效）

SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_R1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_R1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，被合并的分区的索引失效，除非加上update indexes




案例二：合并复合分区list--hash（不加update indexes，本地分区索引、全局分区索引、普通索引都将失效，所以要加update indexes）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_LH1';


（2）查看表的分区是否定义了最大值，也可以通过PL/SQL右键表查看

SQL> select * from user_tab_subpartitions where table_name='TABLE_LH1';


（3）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_LH1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_LH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_LH1_NAME';
SQL> select index_name,partition_name,status from dba_ind_subpartitions where index_name='TABLE_LH1_NAME';


（4）合并2分区

为复合分区合并分区之前，查看分区建表语句，用于合并分区的参考



合并主分区：
SQL> alter table table_lh1 merge partitions p_M,p_W into partition p_W_M [subpartitions number] [update indexes];
subpartitions number：默认不加的话，会继承子分区的模板（如：2个分区都有2个子分区，合并主分区后，1主分区保持2个子分区） ---在合并分区时，into partition的分区名要为下界分区（如：合并p2016,p2017，下界分区名为p2017），不然报：ORA-14275: 不能将下界分区作为结果分区重用

合并子分区：
SQL> alter table table_lh1 merge subpartitions SYS_SUBP95,SYS_SUBP96 into subpartition SYS_SUBP [subpartitions number] [update indexes]; ---不支持与hash相关的表分区。报：ORA-14206-表没有按列表或范围方法进行子分区


SQL> select * from user_tab_subpartitions where table_name='TABLE_LH1';


（5）查看索引是否有效（上步合并复合分区没加update indexes，导致普通索引和创建的分区索引失效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_LH1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_LH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_LH1_NAME';
SQL> select index_name,partition_name,SUBPARTITION_NAME,status from dba_ind_subpartitions where index_name='TABLE_LH1_NAME';


（6）对索引进行重建

SQL> alter index TABLE_LH1_NAME rebuild subpartition SYS_SUBP468 online;
SQL> alter index TABLE_LH1_NAME rebuild subpartition SYS_SUBP469 online; ---分区索引必须对每个分区重建，不能作为整体重建。如果将分区索引作为整体重建会报：ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_LH1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_LH1_NAME';
SQL> select index_name,partition_name,SUBPARTITION_NAME,status from dba_ind_subpartitions where index_name='TABLE_LH1_NAME';






1.7 修改表分区（包括add/drop partition，只对list分区有效）
修改删除list分区值的语法（只对list分区）：

SQL> alter table table_name modify partition partiton_name drop values (‘list分区的值’) [update indexes];
修改添加list分区值的语法（只对list分区）：

SQL> alter table table_name modify partiton partition_name add values (‘添加list分区的值’) [update indexes];


案例一：修改添加（modify....add）list分区一个分区的值（添加的值如在别的分区存在那么会报错，只能添加任何分区都没有的值；不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
（1）查看表的分区类型

SQL> select * from user_part_tables where table_name='TABLE_L1';
SQL> select * from user_tab_partitions where table_name='TABLE_L1';


（2）查看索引状态

SQL> select index_name,status from dba_indexes where table_name='TABLE_L1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_L1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_L1_NAME';


（3）添加par_01分区的值

添加分区内的值之前，查看分区建表语句，用于分区添加的参考



添加的值在default分区中：
SQL> alter table table_l1 modify partition par_01 add values ('山西省'); ---在对list分区的值进行add时，如果该值存在其他分区那么会报ORA-14324: 所要添加的值已存在于 DEFAULT 分区之中（不建议添加list分区中有数据的某个的值，建议新建表，将列的值加到分区中后进行迁移）
注：因为山西省在P_MAX分区（分区值为default）所以不能add，只能通过案例“拆分表分区”进行拆分

添加值为新不在任何分区中：
SQL> alter table table_l1 modify partition par_01 add values ('马来西亚');

SQL> select * from user_tab_partitions where table_name='TABLE_L1'; ---par_01分区的“马来西亚”值添加


（4）查看索引是否有效（上步对分区进行add没有加update indexes，普通索引和本地索引都有效）

SQL> select index_name,status from dba_indexes where table_name='TABLE_L1' and owner='USER';
SQL> select INDEX_NAME,LOCALITY,INTERVAL from dba_part_indexes where table_name='TABLE_L1';
SQL> select index_name,partition_name,status from dba_ind_partitions where index_name='TABLE_L1_NAME'; ---Local索引进行add/drop/split/truncate表的分区时，被修改的分区的索引有效


1.8 修改表分区默认表空间属性（Modify Default Attributes）
一、修改表分区的默认表空间（现有分区的默认表空间不变更，后续增加的会变更）
SQL> alter table table_name modify default attributes tablespace tablespace_name;


二、修改表分区某一个分区的默认表空间（修改一个现有分区的默认表空间）
修改复合分区的默认表空间（只对复合分区有效，亲测范围、hash、列表分区报ORA-14253: 表未按组合分区方法分区）：

SQL> alter table table_name modify default attributes for partition partition_name tablespace tablespace_name [update indexes];


修改表分区某一个分区的默认表空间：

修改分区：
SQL> alter table table_name move partition partiton_name tablespace tablespace_name [update indexes];

修改子分区：
SQL> alter table table_name move subpartition subpartition_name tablespace tablespace_name[update indexes];


案例一：修改现有分区表的表空间，表中有索引存在（不加update indexes全局分区索引、本地分区索引失效，普通索引自动维护，所以要加update indexes）
SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,DEF_TABLESPACE_NAME from dba_part_tables where lower(table_name)='itpux_r1'; ---分区表的默认表空间，如果不修改，后续分区的默认表空间还是SYSTEM


SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from DBA_tab_partitions where lower(table_name)='itpux_r1'; ---分区表现有每个分区的表空间


SQL> select index_name,status,TABLESPACE_NAME from dba_indexes where table_name='ITPUX_L1' and owner='ITPUX';
SQL> select INDEX_NAME,TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_indexes where lower(table_name)='itpux_r1';
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_name in ('IDX_ID'); ---查看分区索引、普通索引的默认表空间和状态




（1）修改分区表的默认属性（现有分区的默认表空间不变更，后续增加的会变更）



SQL> alter table itpux_r1 modify default attributes tablespace itpux;
（2）修改现有分区的默认表空间属性（alter table table_name modify default attributes for partition partition_name tablespace tablespace_name语句只支持复合）

SQL>
alter table itpux_r1 move partition P2014 tablespace itpux parallel 4;
alter table itpux_r1 move partition P2015 tablespace itpux parallel 4;
alter table itpux_r1 move partition P2016 tablespace itpux parallel 4;
alter table itpux_r1 move partition P2017 tablespace itpux parallel 4;
alter table itpux_r1 move partition P_MAX tablespace itpux parallel 4;
（3）移动分区表时，分区索引失效，通过重建索引恢复表(分区索引必须对每个分区重建，不能作为整体重建)

SQL>
alter index IDX_ID rebuild partition P2014 online tablespace itpux;
alter index IDX_ID rebuild partition P2015 online tablespace itpux;
alter index IDX_ID rebuild partition P2016 online tablespace itpux;
alter index IDX_ID rebuild partition P2017 online tablespace itpux;
alter index IDX_ID rebuild partition P_MAX online tablespace itpux;
（4）通过视图验证

SQL> select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,DEF_TABLESPACE_NAME from dba_part_tables where lower(table_name)='itpux_r1'; ---分区表的默认表空间改为itpux


SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from DBA_tab_partitions where lower(table_name)='itpux_r1'; ---分区表现有每个分区的表空间


SQL> select index_name,status,TABLESPACE_NAME from dba_indexes where table_name='ITPUX_L1' and owner='ITPUX';
SQL> select INDEX_NAME,TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_indexes where lower(table_name)='itpux_r1';
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_name in ('IDX_ID'); ---查看分区索引、普通索引的默认表空间和状态




（5）添加一个分区，验证分区是否默认为itpux表空间，索引是不是自动维护和为itpux表空间

SQL> alter table itpux_r1 split partition p_max at (to_date('2018-01-01','YYYY-MM-DD')) into (partition p2018,partition p_max); ---因为有范围分区中有maxvalue分区，所以在添加一个分区时表ora-14074错误，添加的分区的值一定要大于最后一个分区，所以只能拆分p_max分区或者删除后添加

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from DBA_tab_partitions where lower(table_name)='itpux_r1';


SQL> select index_name,status,TABLESPACE_NAME from dba_indexes where table_name='ITPUX_L1' and owner='ITPUX';
SQL> select INDEX_NAME,TABLE_NAME,PARTITIONING_TYPE,DEF_TABLESPACE_NAME from dba_part_indexes where lower(table_name)='itpux_r1';
SQL> select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS,TABLESPACE_NAME from dba_ind_partitions where index_name in ('IDX_ID'); --分区索引的默认表空间会跟着分区表的默认表空间属性一样




1.9 重命名表分区（rename partition）
一、重命名分区语法（不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
SQL> alter table table_name rename partition partition_name to partition_newname [update indexes];


二、重命名子分区语法（不加update indexes，本地分区索引、全局分区索引、普通索引都有效）
SQL> alter table table_name rename subpartition subpartition_name to subpartition_newname [update indexes];

————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/naisiing/article/details/135818347

分区表的管理
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论