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

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;