oracle分区表之何时使用MAXVALUE分区

刚接触这部分内容的小白之前不小心踩到了坑，特写此文章以作警示。

 

2020年10月1日收到反馈，研发的测试环境（一套oracle数据库）某业务表写入报错，写不进数据。

经排查报错信息得知 TEST_TABLE 分区表分区达到上限，需扩展表分区。

--查看表分区情况发现，最大分区正好为当天时间。

set line 200 pagesize 200 col table_name for a10 col partition_name for a25 col high_value for a85 select table_name,partition_name,high_value from dba_tab_partitions where table_name='TEST_TABLE'; TABLE_NAME PARTITION_NAME            HIGH_VALUE ---------- ------------------------- ------------------------------------------------------------------------------------- TEST_TABLE TEST_TABLE_PART1    TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART2    TO_DATE(' 2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PATR3    TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART4    TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART5    TO_DATE(' 2020-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--搜索查询了扩展分区表的命令，并在自己虚拟机环境测试可用后，在研发测试环境执行了扩展分区命令。

--创建新的分区使用的表空间 create tablespace TEST_TABLE_PAT6 datafile '/data/TEST_TABLE_PAT6_01.dbf' size 5G autoextend on; --添加新分区（下个分区时间由业务人员根据数据增量确定的） alter table TEST_TABLE add partition TEST_TABLE_PART6 values less than (TO_DATE('2020-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))     tablespace TEST_TABLE_PAT7     pctfree 10     initrans 1     maxtrans 255     storage     (       initial 64K       next 1M       minextents 1       maxextents unlimited     );

第一次扩展分区，平稳度过。

 

研发人员提醒这是研发用的测试环境，生产环境下有同样的分区表，需要确认下是不是分区也快达到上限需要扩展。

--查看生产环境表分区情况发现，最大分区正好为当月月底，未达到分区上限，但也快了，需要扩展。

set line 200 pagesize 200 col table_name for a10 col partition_name for a25 col high_value for a85 select table_name,partition_name,high_value from dba_tab_partitions where table_name='TEST_TABLE'; TABLE_NAME PARTITION_NAME            HIGH_VALUE ---------- ------------------------- ------------------------------------------------------------------------------------- TEST_TABLE TEST_TABLE_PART1    TO_DATE(' 2019-01-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART2    TO_DATE(' 2019-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PATR3    TO_DATE(' 2020-01-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART4    TO_DATE(' 2020-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART5    TO_DATE(' 2020-10-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PARTMAX  MAXVALUE

此时注意到了生产环境比研发测试环境多了一个分区TEST_TABLE_PARTMAX，特意搜索查询了下该分区的作用，但当时查询后没整明白，于是直接在自己虚拟机环境中加上了该分区测试了一下，发现有该分区存在时，执行上述扩展分区操作会报错ORA-14074: partition bound must collate higher than that of the last partition经查询测试，此时MAXVALUE分区表存在时，需执行如下操作扩展分区。

方式一：删除MAXVALUE上限分区，扩展分区后再添加MAXVALUE分区（若MAXVALUE分区中已有数据，会造成该分区中的数据丢失） --创建新的分区表空间 create tablespace TEST_TABLE_PAT6 datafile '/data/TEST_TABLE_PAT6_01.dbf' size 5G autoextend on; --删除MAXVALUE上限分区 alter table TEST_TABLE drop partition TEST_TABLE_PARTMAX update global indexes; --添加新分区 alter table TEST_TABLE add partition TEST_TABLE_PART6 values less than (TO_DATE('2020-12-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))     tablespace TEST_TABLE_PAT6     pctfree 10     initrans 1     maxtrans 255     storage     (       initial 64K       next 1M       minextents 1       maxextents unlimited     ); --重新添加MAXVALUE上限分区 alter table TEST_TABLE add partition TEST_TABLE_PARTMAX values less than (MAXVALUE) tablespace;   方式二：split扩展分区 --创建新的分区表空间 create tablespace TEST_TABLE_PAT6 datafile '/data/TEST_TABLE_PAT6_01.dbf' size 5G autoextend on; --扩展分区（此命令未指定新新加分区的默认表空间） alter table TEST_TABLE split partition TEST_TABLE_PARTMAX at (TO_DATE('2020-12-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition TEST_TABLE_PART6, partition TEST_TABLE_PARTMAX); --指定新分区的默认表空间 alter table TEST_TABLE move partition TEST_TABLE_PART6 tablespace TEST_TABLE_PAT6; --查询分区使用的表空间 set line 200 pagesize 200 col table_name for a10 col partition_name for a25 col high_value for a85 select table_name,partition_name,tablespace_name from dba_tab_partitions where table_name='TEST_TABLE'; TABLE_NAME PARTITION_NAME            TABLESPACE_NAME ---------- ------------------------- ------------------------------ TEST_TABLE TEST_TABLE_PATR3    TEST_TABLE_PAT3 TEST_TABLE TEST_TABLE_PARTMAX  TEST_TABLE_PATMAX TEST_TABLE TEST_TABLE_PART6    TEST_TABLE_PAT6 TEST_TABLE TEST_TABLE_PART5    TEST_TABLE_PAT5 TEST_TABLE TEST_TABLE_PART4    TEST_TABLE_PAT4 TEST_TABLE TEST_TABLE_PART2    TEST_TABLE_PAT2 TEST_TABLE TEST_TABLE_PART1    TEST_TABLE_PAT1

在自己虚拟机环境测试上述两种方式均可用，且命令执行时间均很快，秒级执行。在研发测试环境使用了方式二：split扩展分区。

第二次扩展分区，平稳度过。

 

2020年12月25日，快元旦放假了，主动查询下所有空间是否够用（系统磁盘，数据库表空间等），此时想起了之前处理过的分区表。查询了下，发现研发环境的分区表已被扩展至2021年7月份，可用分区充足，生产环境未增加新分区。

--查看分区情况 set line 200 pagesize 200 col table_name for a10 col partition_name for a25 col high_value for a85 select table_name,partition_name,high_value from dba_tab_partitions where table_name='TEST_TABLE'; TABLE_NAME PARTITION_NAME            HIGH_VALUE ---------- ------------------------- ------------------------------------------------------------------------------------- TEST_TABLE TEST_TABLE_PART1    TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART2    TO_DATE(' 2019-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PATR3    TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART4    TO_DATE(' 2020-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART5    TO_DATE(' 2020-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PART6    TO_DATE(' 2020-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA TEST_TABLE TEST_TABLE_PARTMAX  MAXVALUE

此时略有疑问，当前时间2020年12月25日，已经过了手动扩展的最大分区的时间范围，为何业务还正常运行，没报错呢？   难道是MAXVALUE分区的作用，这个分区上限是啥？不会是到2021年1月1号吧。（抱着这个莫名的担心和疑问，向业务人员提出了晚上添加下表分区，因之前几次成功添加表分区的经验，虽然此时略有疑问，但没放在心上）问题就此时出现了，现在回想起真的是。。。明明那么平凡，却又那么自信。

为了晚上的扩展分区操作，下午又在自己虚拟环境测试熟悉了下了之前在有MAXVALUE分区时添加表分区的操作，秒级执行成功。（此时自己虚拟机环境与生产环境已经不同了，但平凡又自信的自己就是没发现）晚上扩展表分区操作如下：

方式二：split扩展分区 --创建新的分区表空间 create tablespace TEST_TABLE_PAT7 datafile '/data/TEST_TABLE_PAT7_01.dbf' size 5G autoextend on; --扩展分区（此命令未指定新新加分区的默认表空间）之前这一步秒级执行成功，此时耗时几分钟才执行完 alter table TEST_TABLE split partition TEST_TABLE_PARTMAX at (TO_DATE('2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition TEST_TABLE_PART7, partition TEST_TABLE_PARTMAX); --指定新分区的默认表空间 alter table TEST_TABLE move partition TEST_TABLE_PART7 tablespace TEST_TABLE_PAT7; --查询分区使用的表空间 set line 200 pagesize 200 col table_name for a10 col partition_name for a25 col high_value for a85 select table_name,partition_name,tablespace_name from dba_tab_partitions where table_name='TEST_TABLE'; TABLE_NAME PARTITION_NAME            TABLESPACE_NAME ---------- ------------------------- ------------------------------ TEST_TABLE TEST_TABLE_PATR3    TEST_TABLE_PAT3 TEST_TABLE TEST_TABLE_PARTMAX  TEST_TABLE_PATMAX TEST_TABLE TEST_TABLE_PART7    TEST_TABLE_PAT7 TEST_TABLE TEST_TABLE_PART6    TEST_TABLE_PAT6 TEST_TABLE TEST_TABLE_PART5    TEST_TABLE_PAT5 TEST_TABLE TEST_TABLE_PART4    TEST_TABLE_PAT4 TEST_TABLE TEST_TABLE_PART2    TEST_TABLE_PAT2 TEST_TABLE TEST_TABLE_PART1    TEST_TABLE_PAT1

添加表分区刚完成，应用反馈该分区表涉及的业务全部不可用了，所有业务直接卡死或连接不上，应用监控系统显示该分区表的所有索引全部失效。（添加新分区造成的？之前也没出现过啊，脑海里想着怎样尽快恢复业务，鬼使神差的向研发同事问了句：要不把新加的先分区删掉？）研发的同事也不明白，只想着快速恢复业务，也让我赶紧将新加的分区删掉。删掉分区后，索引仍然全部不可用，只能重建表上所有索引，因应用程序未停止，该分区表又大，重建索引特别慢，让研发同事停止了相关应用后仍然耗时两个多小时才完成该分区表上所有索引的重建工作。之后应用开始验证业务，反馈业务可以连接使用了，也不报警了，本以为本次操作知识只是添加表分区失败，没想到几分钟后群里传来噩耗，表上12月1日到现在的所有数据没了，之前二百多万条，现在都没了。脑海里第一反应不会是删除分区导致的吧？但是新加的分区就能有数据进去？又想了下添加分区操作有点慢，猜测到真有这可能。想着要在天亮前恢复业务，有rman备份但效率太低，开始在网上所有方式，发现可能有用的是第三方的收费工具，但这么晚不一定能联系到人，也不一定好用，还是采用了最慢但最稳妥的恢复方式，rman异机恢复。在另一台新服务器上利用rman备份完成了整个数据库的恢复，因归档日志都在，在数据库日志中又定位到了删除分区操作的具体时间，为最大程度保证数据的完整性，将数据库恢复至了删除分区的前一秒。之后通过kettle工具，利用表中时间戳字段将缺少的数据重新插入到了生产环境中，在早上7点半左右完成了所有的数据恢复工作。

后来又经过各种测试得到如下总结：

不管oracle分区表的MAXVALUE分区是否设置，只要在分区表设置的最大分区时间之前扩展表分区，都没有问题，不会对业务造成影响。但超过最大分区时间再扩展表分区，都会造成影响。

1）不设置MAXVALUE分区时，达到最大分区时间后，分区表无法写入数据，直接影响业务。（但此时手动扩展分区，可以在较短时间内恢复业务） 

2）设置MAXVALUE分区时，达到最大分区时间后，分区表仍然可以写入数据，所有数据存入MAXVALUE分区，短期内不会影响业务，但随着时间增加，MAXVALUE分区内的数据越来越多，分区表所带来的的性能提升会越来越低。

3）设置MAXVALUE分区，且达到最大分区时间后，再添加新分区时会触发以下两个操作：

a.MAXVALUE分区中符合分区时间范围的已有的数据会自动转移到新加分区中。

b.分区表上所有索引失效。（此时需要重建分区表所有索引，索引越多，数据量越大重建时间越长。可参考：两千六百万数据，12个索引重建约耗时2小时，期间分区表涉及的业务均不可用）。

综上所述，为了尽量减少对业务的影响，对于7*24小时的业务，建议不设置MAXVALUE分区。对于不是7*24小时的业务（晚上或周末有空窗期，不办理业务这种）可以设置MAXVALUE分区，在空窗期进行分区表的维护。当然对于分区表的维护，若是可以确保提前规划扩展分区的时间，扩展分区就不会对业务造成影响。