一、问题描述
部署一个数据质量稽核脚本,之前都是正常的,本月统计一直未出结果,日志提示“执行异常:ORA-14400: 插入的分区关键字未映射到任何分区"网上查找了几个问题解决都是说分区表的问题,因生产和指标中都没有用过oracle分区表,之前表的创建表的DDL语句也没有使用分区表语法,对这个问题很疑惑,无从下手解决。
二、问题解决过程
1)上网查找相关资料:
分区表的相关语法
http://blog.csdn.net/hijiankang/article/details/9173877?ticket=ST-26718-Q44fSYfdYx17YM4wd306-passport.csdn.net
ORA-14400问题相关解决办法
http://lsxy117.iteye.com/blog/1407412
2)细化日志,定位存储过程中执行异常的语句为:
insert into REP_DEVICE_ADDRESS_LIST (device_spec_id, device_id, address_num, port_num, free_port_num,version,batch_no,month)
values
(cur.device_spec_id,
cur.device_id,
cur.address_num,
cur.port_num,
cur.free_port_num,
v_version,
v_batchNo,
v_month);
查看表的DDL语句,发现确实是分区表:
CREATE TABLE "ITSP"."REP_DEVICE_ADDRESS_LIST"
( "DEVICE_SPEC_ID" NUMBER,
"DEVICE_ID" NUMBER,
"ADDRESS_NUM" NUMBER,
"PORT_NUM" NUMBER,
"FREE_PORT_NUM" NUMBER,
"VERSION" NUMBER(20,0),
"BATCH_NO" NUMBER,
"MONTH" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ITSP"
PARTITION BY RANGE ("MONTH")
SUBPARTITION BY LIST ("BATCH_NO")
(PARTITION "PART_201409" VALUES LESS THAN (201409)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ITSP"
( SUBPARTITION "PART_201409_P1" VALUES (1)
TABLESPACE "TBS_ITSP"
NOCOMPRESS ) ,
PARTITION "PART_201410" VALUES LESS THAN (201410)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ITSP"
( SUBPARTITION "PART_201410_P1" VALUES (1)
TABLESPACE "TBS_ITSP"
NOCOMPRESS ) ,
PARTITION "PART_201411" VALUES LESS THAN (201411)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ITSP"
( SUBPARTITION "PART_201411_P1" VALUES (1)
TABLESPACE "TBS_ITSP"
NOCOMPRESS ) ,
PARTITION "PART_201501" VALUES LESS THAN (201501)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_ITSP"
( SUBPARTITION "PART_201501_P1" VALUES (1)
TABLESPACE "TBS_ITSP"
NOCOMPRESS ,
SUBPARTITION "PART_201501_1760" VALUES (1760)
TABLESPACE "TBS_ITSP"
NOCOMPRESS ) )
问题终于清楚了,分区表创建按时间分区,而12月份并没有定义对应的分区,导致在执行12份的指标时无法找到对应的表分区进行插入。修改为非分区表,或者增加12月份分区表的定义即可。
再对涉及的中间表进行统计,筛选涉及的中间表中的分区表,进行修改:
SELECT ut.TABLE_NAME,DBMS_METADATA.GET_DDL('TABLE',ut.TABLE_NAME)
FROM USER_TABLES UT
WHERE UT.TABLE_NAME IN ('REPORT_LOG',....中间省略.... 'REP_DEVICE_ADDRESS_LIST');
导出到excle,筛选包含“PARTITION”关键字的表。进行修改,指标执行正常。