ORA-14400: 插入的分区关键字未映射到任何分区 原创

报错信息

Caused by: java.sql.BatchUpdateException: ORA-14400: 插入的分区关键字未映射到任何分区

ORA-14400是表分区出现问题。

复制收展SQL--添加表分区
alter table ER_BILL add partition part2017m3 values less than (to_date('2017-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
alter table ER_BILL add partition part2017m6 values less than (to_date('2017-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
alter table ER_BILL add partition part2017m9 values less than (to_date('2017-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
alter table ER_BILL add partition part2017m12 values less than (to_date('2017-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));

alter table ER_BILL add partition part2018m3 values less than (to_date('2018-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
alter table ER_BILL add partition part2018m6 values less than (to_date('2018-06-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
alter table ER_BILL add partition part2018m9 values less than (to_date('2018-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));
alter table ER_BILL add partition part2018m12 values less than (to_date('2018-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss'));

/*
为表ER_BILL 自动增加分区.分区的列为date类型,分区名类似:part2017m3.
*/
-- 1创建存储过程增加分区
create or replace procedure add_partition_for_erbill
as
  v_year_month_day char(10); -- 分区边界
  v_year char(4); -- 年份（yyyy）,用于组分区名
  v_month varchar2(2);-- 月份，用于组分区名
  v_sql1 varchar2(500); --分区SQL
  v_sql2 varchar2(500); --分区SQL

  begin
    -- 考虑job执行时间为每季度的第一天（如20170701），当时间到20170701时，要新增下一个分区part2017m12,分区边界为 less then 20171201
    select to_char(add_months(sysdate,5),'yyyy-mm-dd') into v_year_month_day from dual;
    select to_char(add_months(sysdate,5),'yyyy') into v_year from dual;
    select to_char(add_months(sysdate,5),'mm') into v_month from dual;

    if v_month < 10 then
      v_month := substr(v_month,2);
    else
      null;
    end if;
    v_sql1 := 'alter table ER_BILL add partition part'||v_year||'m'||v_month||' VALUES LESS THAN ( to_date('''||v_year_month_day||''',''yyyy-mm-dd'') )';
    execute immediate v_sql1;
  end;
/


-- 2创建oracle job自动调用存储过程
declare
  jobid number;
begin
  -- 每季度的第一天凌晨1点执行(如 2017-09-01 01:00:00)   TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
  dbms_job.submit(jobid,'add_partition_for_erbill;',to_date('2018-07-01 02:00:00', 'yyyy-mm-dd hh24:mi:ss'),'TRUNC(ADD_MONTHS(SYSDATE,3),''Q'') + 1/24');
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48