oracle定时添加或删除分区表的分区 存儲過程 增刪分區 操作分區表, 包 pl/sql
程序员文章站
2022-04-21 21:09:41
...
oracle定时添加或删除分区表的分区 存儲過程 增刪分區 操作分區表, 包 pl/sql
本定时创建的是以时间 做为分区表的分区字段 ,字段类型是timestamp
先创建包头:
create or replace package pkg_partition_alter is
/**
date:2010-09-14
author:wanggang
*/
/**
删除分区表中,指定时间段内的分区
####
参数:
v_table_name 分区表名
v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含
v_end_date 删除分区的结束时间 (null 不限定结束时间) 不包含
####
使用注意:
1.假定分区表中分区是按时间戳 timestamp 字段 来分区的
2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的
3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间
4.只有最后一个分区时是无法删除的
**/
procedure drop_partition(v_table_name in varchar2 ,
v_begin_date timestamp default null,
v_end_date timestamp default null);
/**
根据分区信息,以及指定的时间,添加分区
*/
procedure add_partition(v_table_name in varchar2 ,
v_tablespace_name_in in varchar2 default null,
v_begin_date timestamp default null,
v_end_date timestamp);
/**
根据原来的最后分区信息,自动追加分区
*/
procedure append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,
v_end_date in timestamp);
end;
再创建包的实现部分
create or replace package body pkg_partition_alter is
v_pos number := 11;--截断high_value中信息的字符部分起始位
/**
删除分区表中,指定时间段内的分区
####
参数:
v_table_name 分区表名
v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含
v_end_date 删除分区的结束时间 (null 不限定结束时间) 不包含
####
使用注意:
1.假定分区表中分区是按时间戳 timestamp 字段 来分区的
2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的
3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间
4.只有最后一个分区时是无法删除的
**/
procedure drop_partition(
v_table_name in varchar2 ,
v_begin_date timestamp default null,
v_end_date timestamp default null
)
is
---分区表信息
cursor cur_utp(
v_table_name in user_tab_partitions.table_name%TYPE
)
is
select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp
where utp.table_name = UPPER(v_table_name)
order by utp.partition_position ;
v_high_value varchar2(255);--less than value信息
v_partition_max_date timestamp;----less than value信息的 timestamp表示形式
v_sqlexec VARCHAR2(2000); --DDL语句变量
v_count number := 0;
begin
for utp in cur_utp(v_table_name) loop
/* dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name
||' high_value:'||utp.high_value ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);
*/
v_high_value := substr(utp.high_value , v_pos, (utp.high_value_length - v_pos) );
v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');
-- dbms_output.put_line(v_partition_max_date);
if ((v_begin_date is null or v_begin_date <= v_partition_max_date)
and (v_end_date is null or v_partition_max_date < v_end_date)) then
-- dbms_output.put_line('v_begin_date > v_partition_max_date = true:');
-- dbms_output.put_line('v_begin_date:'||to_char(v_begin_date,'syyyy-mm-dd hh24:mi:ss.ff')||' v_partition_max_date:'||to_char(v_partition_max_date,'syyyy-mm-dd hh24:mi:ss.ff'));
v_sqlexec := 'ALTER TABLE ' || utp.table_name || ' DROP PARTITION ' ||
utp.partition_name;
-- dbms_output.put_line('删除' || utp.table_name || '表分区=' || v_SqlExec);
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
v_count := v_count + 1;
end if;
------判断是否不需要再删除 ,跳出循环
if(v_partition_max_date > v_end_date) then
-- dbms_output.put_line('exit start');
exit;
end if;
end loop;
dbms_output.put_line('drop partition count:'||v_count);
end;
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
/**
根据分区信息,以及指定的时间,添加分区
*/
procedure add_partition(v_table_name in varchar2 ,
v_tablespace_name_in in varchar2 default null,
v_begin_date timestamp default null,
v_end_date timestamp)
is
-- Local variables here
i integer;
---分区表信息
cursor cur_utp(
v_table_name in user_tab_partitions.table_name%TYPE
)
is
select * from (select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp
where utp.table_name = UPPER(v_table_name)
order by utp.partition_position desc) utp
where rownum = 1;
v_high_value varchar2(255);--less than value信息
v_partition_max_date timestamp;----less than value信息的 timestamp表示形式
v_sqlexec VARCHAR2(2000); --DDL语句变量
v_count number := 0;
v_interver number := 1;--步长间隔 单位(天)
v_part_name_header varchar2(20) :='part_';
v_part_name varchar2(2000);--分区名
v_partition_num number := 0;
v_tablespace_name varchar2(200) ;
v_end_date_inner timestamp;
begin
v_interver := 1/24;
v_part_name_header := upper(v_part_name_header);
v_end_date_inner := trunc(v_end_date);
--取值
for utp in cur_utp(v_table_name) loop
dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name
||' high_value:'||utp.high_value ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);
v_high_value := substr(utp.high_value , v_pos, (utp.high_value_length - v_pos) );
v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');
if(v_begin_date > v_partition_max_date) then
v_partition_max_date := v_begin_date;
end if;
v_part_name := utp.partition_name ;
--如果没有给默认值
if(v_tablespace_name_in is null) then
-- v_tablespace_name
v_tablespace_name := utp.tablespace_name;
else
v_tablespace_name := v_tablespace_name_in;
end if;
dbms_output.put_line(v_partition_max_date);
end loop;
if( v_part_name_header = substr(v_part_name,1,length(v_part_name_header))) then
dbms_output.put_line('expect part_name header:'||v_part_name);
v_partition_num := to_number(substr(v_part_name,length(v_part_name_header)+1,length(v_part_name)));
v_partition_num := v_partition_num + 1;
else
dbms_output.put_line('not expect part_name header user default:'||v_part_name);
v_partition_num := 0;
end if;
i := 0;
v_partition_max_date := v_partition_max_date + v_interver;
dbms_output.put_line('v_partition_max_date:'||to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')||' v_end_date_inner:' ||to_char(v_end_date_inner, 'syyyy-mm-dd hh24:mi:ss.ff') );
while v_partition_max_date < v_end_date_inner
loop
v_SqlExec := 'ALTER TABLE ' || v_table_name || ' ADD PARTITION ' ||
v_part_name_header || (v_partition_num + i)
|| ' values less than(TIMESTAMP'''||
to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')
|| ''') TABLESPACE ' || v_tablespace_name;
dbms_output.put_line('创建 表分区' || i || '=' || v_SqlExec);
--alter table IP_CONNRATE_LIMITEX_LOG add partition part_0002 values less than(TIMESTAMP'2010-08-31 00:00:00.000000') tablespace NASP_IP_LOG_TABLESPACE;
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
v_partition_max_date := v_partition_max_date + v_interver;
i := i +1;
end loop;
v_count := v_count + i;
dbms_output.put_line('add partition count:'||v_count);
end;
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
/**
根据原来的最后分区信息,自动追加分区
*/
procedure append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,
v_end_date in timestamp)
is
begin
add_partition(v_table_name, v_tablespace_name_in, null,v_end_date);
end;
end;
下面是调用的示例
在job中调用 设定 15天调用一次,为安全,创建和删除 30天的分区
pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);
--可以指定表空间
pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);
job( oracle)
begin
sys.dbms_job.submit(job => :job,
what => pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);',
next_date => to_date('05-10-2010', 'dd-mm-yyyy'),
interval => 'TRUNC(SYSDATE + 15)');
commit;
end;
/
参见我的163博客:http://1985wanggang.blog.163.com/blog/static/7763833201081455532590/
上一篇： 何时使用PartialView方法
下一篇： 总结