Oracle已有表创建LIST分区触发ORA-14400错误问题咨询

报错原因 #

• 你定义的分区规则仅覆盖了REFRESH_FLAG为Y、N两种取值，但表中已有的历史数据存在不符合该规则的记录，包括空值、前后带空格的字符、大小写不匹配的y/n、其他任意非Y/N的取值，Oracle在将存量数据迁移到对应分区时找不到匹配的分区规则，就会抛出ORA-14400错误。
• 额外说明：ALTER TABLE MODIFY PARTITION属于DDL语句，执行后自动提交，你编写的COMMIT语句为冗余内容，不会影响分区创建结果。

解决方案 #

可根据实际业务需求选择以下任意一种方案执行：

方案1：新增默认分区兜底不匹配记录 #

如果允许不符合Y/N规则的记录统一归入兜底分区，直接修改分区创建语句，新增DEFAULT分区即可：

ALTER TABLE "EDW"."LABOR_SCHEDULE_DAY_F" MODIFY
PARTITION BY LIST ("REFRESH_FLAG") 
 (PARTITION "REFRESH_FLAG_Y"  VALUES ('Y') , 
 PARTITION "REFRESH_FLAG_N"  VALUES ('N'),
 PARTITION "REFRESH_FLAG_OTHER" VALUES (DEFAULT)) ;

方案2：清理修正存量数据后创建分区 #

如果业务要求REFRESH_FLAG只能是Y/N两种取值，先执行查询定位不符合规则的记录：

SELECT DISTINCT REFRESH_FLAG FROM EDW.LABOR_SCHEDULE_DAY_F 
WHERE REFRESH_FLAG NOT IN ('Y','N') OR REFRESH_FLAG IS NULL;

根据查询结果将对应记录的REFRESH_FLAG修正为Y或N，清理完成后再执行你原本的分区创建语句即可。

方案3：新增分区覆盖所有存量合法取值 #

如果存量中的非Y/N取值属于合法业务值，直接给这些取值新增对应分区即可，比如存量中存在y、n、空值时，修改语句如下：

ALTER TABLE "EDW"."LABOR_SCHEDULE_DAY_F" MODIFY
PARTITION BY LIST ("REFRESH_FLAG") 
 (PARTITION "REFRESH_FLAG_Y"  VALUES ('Y') , 
 PARTITION "REFRESH_FLAG_N"  VALUES ('N'),
 PARTITION "REFRESH_FLAG_LOWER_Y" VALUES ('y'),
 PARTITION "REFRESH_FLAG_LOWER_N" VALUES ('n'),
 PARTITION "REFRESH_FLAG_NULL" VALUES (NULL)) ;

补充注意事项 #

• 如果使用的Oracle版本低于12cR1，不支持直接通过ALTER TABLE MODIFY PARTITION给存量非分区表添加分区，需使用DBMS_REDEFINITION包做在线重定义完成分区改造，操作前建议提前备份全表数据。

内容的提问来源于stack exchange，提问作者karthik