数据库每年都会新增n个各种类型的分区,手动编写工作量巨大且容易出错。下面说一下当前的脚本实现。
首先有一个存储这些信息的表。

属性名称

类型

备注

OBJ_NAME

VARCHAR2(255)

操作对象名(表或索引)

PARTITION_PRE_NAME

VARCHAR2(255)

分区名前缀

TBS_NAME

VARCHAR2(255)

表空间名

P_FLG

VARCHAR2(10)

分区范围(月天)

P_TYPE

VARCHAR2(10)

分区类型(hash range list)

D_NUM

NUMBER

按天分区则需设置按多少天一个分区

IS_CREATE_SCRIPT

NUMBER

是否需要生成分区脚本(1生成0不需生成)

CREATE_START_TIME

VARCHAR2(20)

分区开始时间

CREATE_END_TIME

VARCHAR2(20)

分区结束时间

CREATE_DEAD_LINE

VARCHAR2(20)

当前已创建分区截止时间

OP_TYPE

VARCHAR2(20)

操作类型(ADD /SPLIT)

SPLIT_PAR_NAME

VARCHAR2(255)

待拆分的分区

OBJ_TYPE

VARCHAR2(20)

操作对象类型(TABLE/INDEX)

这里面的操作类型有两个一个是add另外一个是split,这是因为我们在创建索引的时候有写需要指定一个最大的区间值,下次再扩展的时候就需要split最大的分区块。
在上面的表中,我们维护了自己数据库中需要创建索引的具体信息值。
然后只需要执行下面的存储过程即可以生成相应的分区脚本了。

?

create or replace procedure add_additional_partition2 is
  str varchar2(8000);
  dtr varchar2(4);
  j   number default 0;
  i   number default 0;
 
  start_date date;
  end_date   date;
  cur_date   date;
 
  str_cur_date varchar2(20);
  par_name     varchar2(100);
 
  tbsname             varchar2(100);
  objname             varchar2(100);
  curobjname          varchar2(100);
  pre_name            varchar2(100);
  flg                 varchar2(100);
  ptype               varchar2(100);
  pdaynum             number;
  pnumber             number;
  optype              varchar2(20);
  objtype             varchar2(20);
  splitparname        varchar2(255);
  v_create_start_time varchar2(20);
  v_create_end_time   varchar2(20);
 
  CURSOR C_PARTITION_TABLE IS
    SELECT OBJ_NAME,
           PARTITION_PRE_NAME,
           TBS_NAME,
           P_FLG,
           P_TYPE,
           D_NUM,
           CREATE_START_TIME,
           CREATE_END_TIME,
           OP_TYPE,
           SPLIT_PAR_NAME,
           OBJ_TYPE
      FROM GE_T_PARTITION_INFO
     WHERE IS_CREATE_SCRIPT = 1;
 
begin
   
  dbms_output.enable(999999999999999999999);
  dtr := '';
 
  FOR R_PAR_TABLE IN C_PARTITION_TABLE LOOP
 
 
    tbsname             := R_PAR_TABLE.TBS_NAME;
    objname             := R_PAR_TABLE.OBJ_NAME;
    pre_name            := R_PAR_TABLE.PARTITION_PRE_NAME;
    v_create_start_time := R_PAR_TABLE.CREATE_START_TIME;
    v_create_end_time   := R_PAR_TABLE.CREATE_END_TIME;
    flg                 := R_PAR_TABLE.P_FLG;
    ptype               := R_PAR_TABLE.P_TYPE;
    pdaynum             := R_PAR_TABLE.D_NUM;
    pnumber             := R_PAR_TABLE.D_NUM;
    optype              := R_PAR_TABLE.OP_TYPE;
    splitparname        := R_PAR_TABLE.SPLIT_PAR_NAME;
    objtype             := R_PAR_TABLE.OBJ_TYPE;
    start_date          := to_date(v_create_start_time,'yyyy-MM-dd hh24:Mi:ss');
    end_date            := to_date(v_create_end_time,'yyyy-MM-dd hh24:Mi:ss');
 
    if curobjname is null then
       curobjname := objname;
    elsif curobjname is not null and curobjname <> objname then
       update ge_t_partition_info set create_dead_line=str_cur_date where obj_name=curobjname;
       curobjname := objname;
       commit;
    end if;
 
    cur_date := start_date;
 
    if upper(ptype) = 'RANGE' then
      if upper(flg) = 'M' then
        while (cur_date <= end_date) loop
          par_name := pre_name || '_P' || to_char(cur_date, 'yyyyMM');
 
          str_cur_date := to_char(last_day(trunc(cur_date, 'DD')) + 1,'yyyy-MM-dd hh24:mi:ss');
          if upper(optype) = 'ADD' then
             select 'ALTER '||objtype||' '|| objname || ' ADD PARTITION ' || par_name ||
                   ' VALUES LESS THAN (TO_DATE(''' || str_cur_date ||
                   ''',''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || tbsname || ';'
              into str
              from dual;
          elsif upper(optype) = 'SPLIT' then
             select 'ALTER '||objtype||' '|| objname || ' SPLIT PARTITION ' || splitparname ||
                   ' AT (TO_DATE(''' || str_cur_date ||
                   ''',''YYYY-MM-DD HH24:MI:SS'')) INTO (PARTITION '||par_name||' TABLESPACE '||tbsname||',PARTITION '||splitparname||') ;'
              into str
              from dual;
          end if;
          dbms_output.put_line(str);
 
          if (cur_date <= end_date) then
            cur_date := add_months(cur_date, 1);
          end if;
        end loop;
      end if;
      if upper(flg) = 'D' then
        cur_date := cur_date + pdaynum;
        while (cur_date <= end_date) loop
          i            := i + 1;
          par_name     := pre_name || '_P' || to_char(cur_date, 'yyyyMM') || '_' || to_char(i);
          str_cur_date := to_char(cur_date, 'yyyy-MM-dd hh24:mi:ss');
          if upper(optype) = 'ADD' then
                select 'ALTER '||objtype||' '|| objname || ' ADD PARTITION '  || par_name ||
                       ' VALUES LESS THAN (TO_DATE(''' || str_cur_date ||
                       ''',''YYYY-MM-DD HH24:MI:SS'')) TABLESPACE ' || tbsname || ';'
                  into str
                  from dual;
          elsif upper(optype) = 'SPLIT' then
               select 'ALTER '||objtype||' '|| objname || ' SPLIT PARTITION ' || splitparname ||
                     ' AT (TO_DATE(''' || str_cur_date ||
                     ''',''YYYY-MM-DD HH24:MI:SS'')) INTO (PARTITION '||par_name||' TABLESPACE '||tbsname||',PARTITION '||splitparname||') ;'
                into str
                from dual;
          end if;
          dbms_output.put_line(str);
 
          if (to_char(cur_date, 'yyyyMM') <> to_char(cur_date + pdaynum, 'yyyyMM')) then
            i := 0;
          end if;
          if (cur_date < end_date and (cur_date + pdaynum) >= end_date) then
            cur_date := end_date;
          else
            cur_date := cur_date + pdaynum;
          end if;
        end loop;
 
      end if;
 
    end if;
 
    if upper(ptype) = 'HASH' then
      FOR j IN 1 .. pnumber LOOP
        select 'ALTER '||objtype||' '|| objname || ' ADD PARTITION ' || objname ||
               '_PHASH_' || j || ' TABLESPACE ' || tbsname || ';'
          into str
          from dual;
        dbms_output.put_line(str);
      end loop;
    end if;
  END LOOP;
 
end add_additional_partition2;

执行命令

?

set serveroutput on;
exec ADD_ADDITIONAL_PARTITION2;