Oracle 自动拓展分区的实现（11g后使用interval分区）

Oracle 自动拓展分区的实现（11g后使用interval分区）

创建 ERROR TABLE :

create sequence seq_error_log_id ;
create table error_log (id number , unit_name varchar2 (30),
                            err_code varchar2(100 ) ,
                            err_desc varchar2(4000 ),
                            err_date date) ;

自动拓展分区包：

create or replace package dba_tools is

  -- Author  : RenFj
  -- Created : 7/24/2013 9:26:12 AM
  -- Purpose : My DBA's utility
  --
  --
  YEAR constant varchar2(1) := 'Y' ;
  MONTH constant varchar2(1) := 'M' ;
  DAY constant varchar2(1) := 'D' ;

 

 

QUARTER constant varchar2(1) := 'Q' ;

-- ******No matter you understand it or not , I do .******
  -- create table log_errors (id number , unit_name varchar2(30),
  --                          err_code varchar2(100) ,
  --                          err_desc varchar2(4000),
  --                          err_date date) ;
  -- Log errors which raised in the programs .
  -- p_unit_name              program name
  -- p_sqlc                    error code (sqlcode)
  -- p_sqler                  error message (sqlerrm)
  --
 
 
  procedure err_log(p_unit_name varchar2,
                    p_sqlc      varchar2,
                    p_sqler    varchar2);

 
  -- Auto extend partitions with the specificed table in the program .
  -- tb_name    Specified table name which need to extend partitions.
  -- part_iden  Partition identifier
  -- The following identifier can be used :
  --    d        day
  --    m        month
  --    q        quarter
  --    y        year
  -- interval_num  Interval number , that means the additional partition values less than (sysdate+part_iden*interval_num)
  -- This program need partition's name has roles . Like ($table_name||$lessthan_date_string)order_20130101
  procedure dba_autoextend_partitions(p_tb_name      varchar2,
                                      p_part_iden    varchar2,
                                      p_interval_num number);

end dba_tools;

create or replace package body dba_tools is

  procedure err_log(p_unit_name varchar2,
                    p_sqlc      varchar2,
                    p_sqler    varchar2) is
    PRAGMA AUTONOMOUS_TRANSACTION ; --autonomous transaction .
  begin
    -- No exception handle in this unit .
    insert into error_log
      (id, unit_name, err_code, err_desc, err_date)
    values
      (seq_error_log_id.nextval, p_unit_name, p_sqlc, p_sqler, sysdate);
    commit;
  end;

  procedure dba_autoextend_partitions(p_tb_name      varchar2,
                                      p_part_iden    varchar2,
                                      p_interval_num number) is
    d_last_part_dat      date;          --最后一个partition 范围中的截止时间
    d_next_dat          date;          --这一次的创建的partition的截止时间
    v_part_less_than_val varchar2(4000 ); --less than 字符串
    v_part_name          varchar2(30 )  ; --partition 的名称
    v_add_part_sql      varchar2(4000 ); --add partition 的sql语句
  begin
    --查询出最后一个创建的partition的截止时间，partition的名称格式必须为xxxx_yyyymmdd
    --例如orders_20120101 其中20120101表示创建的partition的截止时间
    select to_date(regexp_replace(partition_name,
                                  '(.*)(2[0-9]{3}[0-9]{4})',
                                  '\2'),
                  'yyyymmdd')
      into d_last_part_dat
      from (select max(partition_position) over( partition by table_name) mn,
                  t.*
              from user_tab_partitions t
            where table_name = upper(p_tb_name)) t
    where t.mn = partition_position;
    --根据参数p_part_iden以及p_interval_num计算出这一次创建的partition的截止时间
    --d 表示天，m表示月，q表示季度，y表示年度
    if p_part_iden in ('d', 'D') then
      d_next_dat := d_last_part_dat + p_interval_num;
    elsif p_part_iden in ('m', 'M') then
      d_next_dat := add_months(trunc(d_last_part_dat, 'MM'), p_interval_num);
    elsif p_part_iden in ('q', 'Q') then
      d_next_dat := add_months(trunc(d_last_part_dat, 'Q'),
                              3 * p_interval_num);
    elsif p_part_iden in ('y', 'Y') then
      d_next_dat := add_months(trunc(d_last_part_dat, 'Q'),
                              12 * p_interval_num);
    else
      raise_application_error(- 20999,
                              'Type following identifier with p_part_iden : d(day) , m(month) , q(quarter) , y(year) .');
    end if ;
    --根据截止时间拼装分区名称v_part_name 以及 less than 子句v_part_less_than_val
    --example: v_part_name := ORDERS_20120101
    --        v_part_less_than_val := TO_DATE(TO_CHAR(20120101,'YYYYMMDD'))
    v_part_name          := upper(p_tb_name) || '_' ||
                            to_char(d_next_dat, 'yyyymmdd');
    v_part_less_than_val := 'to_date(' || to_char(d_next_dat, 'yyyymmdd') ||
                            ',''yyyymmdd'')';
    --拼装的添加分区的sql语句v_add_part_sql
    -- example : ALTER TABLE print_media_part ADD PARTITION p4 VALUES LESS THAN (400)
    v_add_part_sql := 'ALTER TABLE '
                  || p_tb_name
                  || ' ADD PARTITION '
                  || v_part_name
                  || ' VALUES LESS THAN ('
                  || v_part_less_than_val
                  || ')';
 
    execute immediate v_add_part_sql;
  exception
    when others then
      err_log( 'dba_autoextend_partitions', sqlcode , sqlerrm);
  end;

end dba_tools;

创建测试表