注：有些表被初期或者后期改造成分区表，为了保证分区数据能正常插入，有些习惯再最后设置一个maxvalue分区，但一旦过多数据超过maxvalue的上一个分区，就会造成maxvalue分区有大量数据，严重影响性能。
带有maxvalue的分区无法像普通分区直接在最大分区后执行add添加新分区，要使用split来对maxvalue分区进行拆分，但在拆分时一定要先充分测试，尽量减少对生产环境的影响。
测试过程
一、创建分区表
– 创建分区表
create table p_range_test (id number,name varchar2(100))
partition by range(id)(
partition t_p1 values less than (10),
partition t_p2 values less than (20),
partition t_p3 values less than (30),
partition t_pmax values less than (maxvalue));
-- 查询当前分区表信息
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
P_RANGE_TEST T_P1 10 T_HS_MESSAGE
P_RANGE_TEST T_P2 20 T_HS_MESSAGE
P_RANGE_TEST T_P3 30 T_HS_MESSAGE
P_RANGE_TEST T_PMAX MAXVALUE T_HS_MESSAGE
二、插入数据
-- 插入数据
insert into p_range_test values (1,'A');
insert into p_range_test values (2,'B');
insert into p_range_test values (3,'C');
insert into p_range_test values (4,'D');
insert into p_range_test values (5,'E');
insert into p_range_test values (6,'F');
insert into p_range_test values (7,'G');
insert into p_range_test values (8,'H');
insert into p_range_test values (9,'I');
insert into p_range_test values (10,'AA');
insert into p_range_test values (11,'BB');
insert into p_range_test values (12,'CC');
insert into p_range_test values (13,'DD');
insert into p_range_test values (14,'EE');
insert into p_range_test values (15,'FF');
insert into p_range_test values (16,'GG');
insert into p_range_test values (17,'HH');
insert into p_range_test values (18,'II');
insert into p_range_test values (19,'II');
insert into p_range_test values (20,'AAA');
insert into p_range_test values (21,'BBB');
insert into p_range_test values (22,'CCC');
insert into p_range_test values (23,'DDD');
insert into p_range_test values (24,'EEE');
insert into p_range_test values (25,'FFF');
insert into p_range_test values (26,'GGG');
insert into p_range_test values (27,'HHH');
insert into p_range_test values (28,'III');
insert into p_range_test values (29,'III');
insert into p_range_test values (30,'AAAA');
insert into p_range_test values (31,'BBBB');
insert into p_range_test values (32,'CCCC');
insert into p_range_test values (33,'DDDD');
insert into p_range_test values (34,'EEEE');
insert into p_range_test values (35,'FFFF');
insert into p_range_test values (36,'GGGG');
insert into p_range_test values (37,'HHHH');
insert into p_range_test values (38,'IIII');
insert into p_range_test values (39,'IIII');
insert into p_range_test values (40,'AAAAA');
insert into p_range_test values (41,'BBBBB');
insert into p_range_test values (42,'CCCCC');
insert into p_range_test values (43,'DDDDD');
insert into p_range_test values (44,'EEEEE');
insert into p_range_test values (45,'FFFFF');
insert into p_range_test values (46,'GGGGG');
insert into p_range_test values (47,'HHHHH');
insert into p_range_test values (48,'IIIII');
insert into p_range_test values (49,'IIIII');
insert into p_range_test values (50,'AAAAAA');
insert into p_range_test values (51,'BBBBBB');
insert into p_range_test values (52,'CCCCCC');
insert into p_range_test values (53,'DDDDDD');
insert into p_range_test values (54,'EEEEEE');
insert into p_range_test values (55,'FFFFFF');
insert into p_range_test values (56,'GGGGGG');
insert into p_range_test values (57,'HHHHHH');
insert into p_range_test values (58,'IIIIII');
insert into p_range_test values (59,'IIIIII');
commit;
-- 查询各分区对应数据信息
SQL> select * from p_range_test partition(t_p1);
ID NAME
---------- ----------------------------------------------------------------------------------------------------
1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
9 rows selected.
SQL> select * from p_range_test partition(t_p2);
ID NAME
---------- ----------------------------------------------------------------------------------------------------
10 AA
11 BB
12 CC
13 DD
14 EE
15 FF
16 GG
17 HH
18 II
19 II
10 rows selected.
SQL> select * from p_range_test partition(t_p3);
ID NAME
---------- ----------------------------------------------------------------------------------------------------
20 AAA
21 BBB
22 CCC
23 DDD
24 EEE
25 FFF
26 GGG
27 HHH
28 III
29 III
10 rows selected.
select * from p_range_test partition(t_pmax);
SQL> select * from p_range_test partition(t_pmax);
ID NAME
---------- ----------------------------------------------------------------------------------------------------
30 AAAA
31 BBBB
32 CCCC
33 DDDD
34 EEEE
35 FFFF
36 GGGG
37 HHHH
38 IIII
39 IIII
40 AAAAA
41 BBBBB
42 CCCCC
43 DDDDD
44 EEEEE
45 FFFFF
46 GGGGG
47 HHHHH
48 IIIII
49 IIIII
50 AAAAAA
51 BBBBBB
52 CCCCCC
53 DDDDDD
54 EEEEEE
55 FFFFFF
56 GGGGGG
57 HHHHHH
58 IIIIII
59 IIIIII
30 rows selected.
三、拆分分区
-- 无法对含有maxvalue分区表使用add partition 方式添加分区
SQL> alter table p_range_test add partition t_p4 values less than(40);
alter table p_range_test add partition t_p4 values less than(40)
*
ERROR at line 1:
ORA-14074: 分区界限必须调整为高于最后一个分区界限
-- 使用split拆分分区
-- 使用此方式会默认创建以SYS开头的新分区
SQL> alter table p_range_test split partition t_pmax at (40) into (partition, partition t_pmax);
Table altered.
-- 查询分区表信息
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='P_RANGE_TEST' order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
P_RANGE_TEST T_P1 10 T_HS_MESSAGE
P_RANGE_TEST T_P2 20 T_HS_MESSAGE
P_RANGE_TEST T_P3 30 T_HS_MESSAGE
P_RANGE_TEST SYS_P822 40 T_HS_MESSAGE
P_RANGE_TEST T_PMAX MAXVALUE T_HS_MESSAGE
SQL> select * from p_range_test partition(sys_p822);
ID NAME
---------- ----------------------------------------------------------------------------------------------------
30 AAAA
31 BBBB
32 CCCC
33 DDDD
34 EEEE
35 FFFF
36 GGGG
37 HHHH
38 IIII
39 IIII
10 rows selected.
SQL> select * from p_range_test partition(t_pmax);
ID NAME
---------- ----------------------------------------------------------------------------------------------------
40 AAAAA
41 BBBBB
42 CCCCC
43 DDDDD
44 EEEEE
45 FFFFF
46 GGGGG
47 HHHHH
48 IIIII
49 IIIII
50 AAAAAA
51 BBBBBB
52 CCCCCC
53 DDDDDD
54 EEEEEE
55 FFFFFF
56 GGGGGG
57 HHHHHH
58 IIIIII
59 IIIIII
20 rows selected.
四、修改新分区名称
SQL> alter table p_range_test rename partition SYS_P822 to t_p4;
Table altered.
SQL> select * from p_range_test partition(t_p4);
30 AAAA
31 BBBB
32 CCCC
33 DDDD
34 EEEE
35 FFFF
36 GGGG
37 HHHH
38 IIII
39 IIII
10 rows selected.
SQL> select * from p_range_test partition(t_pmax);
40 AAAAA
41 BBBBB
42 CCCCC
43 DDDDD
44 EEEEE
45 FFFFF
46 GGGGG
47 HHHHH
48 IIIII
49 IIIII
50 AAAAAA
51 BBBBBB
52 CCCCCC
53 DDDDDD
54 EEEEEE
55 FFFFFF
56 GGGGGG
57 HHHHHH
58 IIIIII
59 IIIIII
20 rows selected.
备注
1）拆分并指定分区名
对于以上测试中第三和第四步骤，可以采用一步操作
alter table p_range_test split partition t_pmax at (40) into (partition t_p50, partition t_pmax);
以时间做分区进行split
alter table TEST.TB1 split partition P_MAX_VALUE at (TO_DATE('2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition PART2023_05, partition P_MAX_VALUE);
alter table TEST.TB2 split partition P_MAX_VALUE at (TO_DATE('2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition PART2023_06, partition P_MAX_VALUE);
2）split分区会导致局部分区索引失效
#3）split分区可能会导致ora-01652报错
最后修改时间：2023-01-31 14:06:03
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
