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
尽情测试吧。