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尽情测试吧。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Oracle PL/SQL常用命令Oracle中利用expdp/impdp备份数据库的使用说明相关资讯 Oracle 11g Oracle 11g分区 Oracle interval

Oracle 11g导入到10g引起的错误（11/16/2015 10:55:27）
Oracle 11g 导库导不出空表问题（08/19/2015 19:55:58）
Oracle 11g维护分区（五）—— （08/05/2015 11:05:20）

Oracle 11gClone安装方法（08/24/2015 20:25:41）
Oracle 11g中和SQL TUNING相关的新（08/12/2015 11:22:52）
Oracle 11g维护分区（六）—— （08/05/2015 11:03:54）

本文评论查看全部评论（0）