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;

创建测试表

CREATE TABLE orders
( prod_id NUMBER(6)
, order_time DATE
, quantity NUMBER(3)
, price NUMBER(10,2)
)
tablespace users
PARTITION BY RANGE (order_time)
( PARTITION order_before VALUES LESS THAN (TO_DATE('20100101','yyyymmdd'))
, PARTITION order_20100101 VALUES LESS THAN (TO_DATE('20110101','yyyymmdd'))
, PARTITION order_20110101 VALUES LESS THAN (TO_DATE('20120101','yyyymmdd'))
, PARTITION order_20120101 VALUES LESS THAN (TO_DATE('20130101','yyyymmdd'))
);

测试：

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='ORDERS' ;
 
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS                         ORDER_20100101                 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20110101                 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20120101                 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_BEFORE                   TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 

SQL> exec dba_tools.dba_autoextend_partitions('ORDERS','y',2) ;
 
PL/SQL procedure successfully completed
 
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='ORDERS' ;
 
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS                         ORDERS_20140101                TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20100101                 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20110101                 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20120101                 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_BEFORE                   TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 

SQL> exec dba_tools.dba_autoextend_partitions('ORDERS','q',2) ;
 
PL/SQL procedure successfully completed
 
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='ORDERS' ;
 
TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
ORDERS                         ORDERS_20140101                TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDERS_20140701                TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20100101                 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20110101                 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_20120101                 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         ORDER_BEFORE                   TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 
6 rows selected

尽情测试吧。

如需转载，请注明出处：

blog.csdn.net/renfengjun 或者 www.orcl.cc