ORA-14400 inserted partition key does not map to any partition错误
Jul302015
这几天每天都在通宵，白天休息时间很少，身体有些吃不消，还好总监又带了两个DBA过来，白天终于可以好好休息了，正在酒店睡觉，被DBA的电话叫醒，说是遇到了分区不存在导致数据无法插入的错误，那张表是按照时间字段的RANGE分区，使用11g的新特性，自动分区(INTERVAL)，一天一个分区，怎么会遇到找不到分区的错误呢？出问题的数据是2015年7月21号的数据，最近也是通宵比较多，一时没反应过来，第一反应是不是闰秒的问题导致的，可是在去现场的路上想了下，7月21号早就过了闰秒的日期了。
到了现场，其他DBA手动添加了分区，解决了数据插不进去的问题，我和DBA说那个分区表是自动扩展分区的，不需要手动维护。怎么会出现这个问题，问他看没看告警日志，然后他一拍大腿，说那张表他DROP后重建的，重建的时候没有指定自动扩展分区，他是使用PLSQL Developer工具右键查看表，查看SQL，使用这个SQL创建的，这个表当时的分区停留在7月21号之前，所以他创建的表只有7月21号之前的分区，7月21号及之后的数据就找不到对应的分区了。
以下模拟下这个过程，先创建分区表ORD_PAY_PART。
SQL> CREATE TABLE "CHGSHS"."ORD_PAY_PART"
2 ( "ORD_ID" VARCHAR2(40) NOT NULL ENABLE,
3 "PAY_SEQ" VARCHAR2(5) NOT NULL ENABLE,
4 "PAY_MNS_CD" VARCHAR2(7) NOT NULL ENABLE,
5 "PAY_ORD_PTR_CD" VARCHAR2(7) NOT NULL ENABLE,
6 "PAY_ARR_AMT" NUMBER(21,2) NOT NULL ENABLE,
7 "PAY_AMT" NUMBER(21,2),
8 "PAY_DTM" DATE,
9 "PAY_MNS_RLTD_ID" VARCHAR2(40),
10 "RFN_AMT" NUMBER(21,2) DEFAULT 0,
11 "RFN_PSS_AMT" NUMBER(21,2) DEFAULT 0,
12 "ORG_ORD_ID" VARCHAR2(40) NOT NULL ENABLE,
13 "ORG_PAY_SEQ" VARCHAR2(5) NOT NULL ENABLE,
14 "ORG_MNS_CD" VARCHAR2(7) NOT NULL ENABLE,
15 "INST_ID" VARCHAR2(40) NOT NULL ENABLE,
16 "INST_DTM" DATE DEFAULT SYSDATE NOT NULL ENABLE,
17 "MDF_ID" VARCHAR2(40) NOT NULL ENABLE,
18 "MDF_DTM" DATE DEFAULT SYSDATE NOT NULL ENABLE,
19 CONSTRAINT "PK_ORD_PAY_1" PRIMARY KEY ("ORD_ID", "PAY_MNS_CD")
20 USING INDEX
21 TABLESPACE "TS_INX_ORD" ENABLE
22 )
23 TABLESPACE "TS_ORD"
24 PARTITION BY RANGE ("MDF_DTM") INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
25 (PARTITION "P1" VALUES LESS THAN
26 (TO_DATE(' 2013-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
27 SEGMENT CREATION IMMEDIATE
28 TABLESPACE "TS_ORD" )
29 ;
Table created.
插入7月1号之前的数据。
SQL> insert /* +append */ into "CHGSHS"."ORD_PAY_PART" select * from "CHGSHS"."ORD_PAY" where MDF_DTM < to_date('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
14414226 rows created.
SQL> commit;
Commit complete.
此时，分区停留在7月1日之前，7月1日之后的分区不存在。使用PLSQL Developer工具查看表并查看的SQL，而不是METADABA DDL操作。这样看到的SQL并不是自带扩展分区的，而是列出了所有的分区。根据查看到的SQL创建分区表。
SQL> drop table chgshs.ORD_PAY_PART purge;
Table dropped.
SQL> create table CHGSHS.ORD_PAY_PART
2 (
3 ord_id VARCHAR2(40) not null,
4 pay_seq VARCHAR2(5) not null,
5 pay_mns_cd VARCHAR2(7) not null,
6 pay_ord_ptr_cd VARCHAR2(7) not null,
7 pay_arr_amt NUMBER(21,2) not null,
8 pay_amt NUMBER(21,2),
9 pay_dtm DATE,
10 pay_mns_rltd_id VARCHAR2(40),
11 rfn_amt NUMBER(21,2) default 0,
12 rfn_pss_amt NUMBER(21,2) default 0,
13 org_ord_id VARCHAR2(40) not null,
14 org_pay_seq VARCHAR2(5) not null,
15 org_mns_cd VARCHAR2(7) not null,
16 inst_id VARCHAR2(40) not null,
17 inst_dtm DATE default SYSDATE not null,
18 mdf_id VARCHAR2(40) not null,
19 mdf_dtm DATE default SYSDATE not null
20 )
21 partition by range (MDF_DTM)
22 (
23 partition P1 values less than (TO_DATE(' 2013-08-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
24 tablespace TS_ORD
25 pctfree 10
26 initrans 1
27 maxtrans 255
28 storage
29 (
30 initial 8M
31 next 1M
32 minextents 1
33 maxextents unlimited
34 ),
35 partition SYS_P4319 values less than (TO_DATE(' 2013-08-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
36 tablespace TS_ORD
37 pctfree 10
38 initrans 1
39 maxtrans 255
40 storage
41 (
42 initial 8M
43 next 1M
44 minextents 1
45 maxextents unlimited
46 ),
--由于分区太多，建表的SQL8千多行，此处省略大部分分区相关的代码
8123 partition SYS_P4978 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
8124 tablespace TS_ORD
8125 pctfree 10
8126 initrans 1
8127 maxtrans 255
8128 storage
8129 (
8130 initial 8M
8131 next 1M
8132 minextents 1
8133 maxextents unlimited
8134 )
8135 );
Table created.
SQL> alter table CHGSHS.ORD_PAY_PART
2 add constraint PK_ORD_PAY_1 primary key (ORD_ID, PAY_MNS_CD)
3 using index
4 tablespace TS_INX_ORD
5 pctfree 10
6 initrans 2
7 maxtrans 255
8 storage
9 (
10 initial 64K
11 next 1M
12 minextents 1
13 maxextents unlimited
14 );
Table altered.
从建表的SQL可以看到，此时的表分区停留在7月1号之前，而且没有DEFAULT分区，7月1日之后的数据将插入不进去。
SQL> insert /* +append */ into "CHGSHS"."ORD_PAY_PART" select /* +parallel 8 */ * from "CHGSHS"."ORD_PAY" where MDF_DTM >to_date('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
insert /* +append */ into "CHGSHS"."ORD_PAY_PART" select /* +parallel 8 */ * from "CHGSHS"."ORD_PAY" where MDF_DTM >to_date('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
手动添加分区之后，可以插入数据。
SQL> alter table CHGSHS.ORD_PAY_PART ADD partition P2 values less than (TO_DATE(' 2015-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace TS_ORD;
Table altered.
SQL> insert /* +append */ into "CHGSHS"."ORD_PAY_PART" select /* +parallel 8 */ * from "CHGSHS"."ORD_PAY" where MDF_DTM >to_date('2015-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss') AND MDF_DTM <to_date('2015-07-02 00:00:00','yyyy-mm-dd hh24:mi:ss');
26120 rows created.
SQL> COMMIT;
Commit complete.
11g可以将分区表修改为字段扩展分区，就不需要手动添加分区了。
SQL> alter table CHGSHS.ORD_PAY_PART set INTERVAL (NUMTODSINTERVAL(1, 'DAY'));
Table altered.
SQL>
SQL>
SQL> insert /* +append */ into "CHGSHS"."ORD_PAY_PART" select /* +parallel 8 */ * from "CHGSHS"."ORD_PAY" where MDF_DTM >to_date('2015-07-02 00:00:00','yyyy-mm-dd hh24:mi:ss');
501418 rows created.
SQL> commit;
Commit complete.
如果数据库打开了enable_ddl_logging参数，在告警日志就可以看到扩展分区的信息。
SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
enable_ddl_logging boolean TRUE
Wed Jul 29 22:14:46 2015
alter table CHGSHS.ORD_PAY_PART set INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
Wed Jul 29 22:15:07 2015
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4986 (682) VALUES LESS THAN (TO_DATE(' 2015-07-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4987 (680) VALUES LESS THAN (TO_DATE(' 2015-07-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4988 (677) VALUES LESS THAN (TO_DATE(' 2015-07-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4989 (678) VALUES LESS THAN (TO_DATE(' 2015-07-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4990 (697) VALUES LESS THAN (TO_DATE(' 2015-07-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4991 (683) VALUES LESS THAN (TO_DATE(' 2015-07-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4992 (686) VALUES LESS THAN (TO_DATE(' 2015-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4993 (681) VALUES LESS THAN (TO_DATE(' 2015-07-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4994 (685) VALUES LESS THAN (TO_DATE(' 2015-07-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4995 (684) VALUES LESS THAN (TO_DATE(' 2015-07-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4996 (679) VALUES LESS THAN (TO_DATE(' 2015-07-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4997 (698) VALUES LESS THAN (TO_DATE(' 2015-07-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4998 (691) VALUES LESS THAN (TO_DATE(' 2015-07-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P4999 (689) VALUES LESS THAN (TO_DATE(' 2015-07-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5000 (692) VALUES LESS THAN (TO_DATE(' 2015-07-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5001 (690) VALUES LESS THAN (TO_DATE(' 2015-07-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5002 (693) VALUES LESS THAN (TO_DATE(' 2015-07-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5003 (687) VALUES LESS THAN (TO_DATE(' 2015-07-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5004 (688) VALUES LESS THAN (TO_DATE(' 2015-07-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5005 (699) VALUES LESS THAN (TO_DATE(' 2015-07-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5006 (702) VALUES LESS THAN (TO_DATE(' 2015-07-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLE CHGSHS.ORD_PAY_PART: ADDED INTERVAL PARTITION SYS_P5007 (703) VALUES LESS THAN (TO_DATE(' 2015-07-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
11g的这个字段扩展分区还是很方便的，10g的时候通常要写存储过程来扩展分区，11g自己就可以做这个事情。
ORA-14400 inserted partition key does not map to any partition错误：等您坐沙发呢！
发表评论
