欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-28 12:50:28
...

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;

创建测试表