oracle自动扩展分区是从11g,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);

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

尽情测试吧。