Oracle分区维护（使用procedure）
Oracle分区维护（使用procedure）
Java代码
create or replace procedure pro_maintain_partition( tab_name in varchar2)
as
var_tab_name varchar2(32) ;
var_tab_bak_name varchar2(32) ;
var_partition varchar2(32) ;
var_less_than varchar2(8) ;
var_flag number ;
begin
select upper(tab_name) into var_tab_name from dual ;
select upper(tab_name||'_bak') into var_tab_bak_name from dual ;
select count(1) into var_flag from user_segments
where segment_name = var_tab_name
and partition_name like '%'||to_char(sysdate , 'yyyy_mm') ;
select var_tab_name||'_'||to_char(sysdate , 'yyyy_mm') into var_partition from dual ;
select to_char(add_months(sysdate , 1) , 'yyyymm')||'01' into var_less_than from dual ;
if var_flag = 0 then
--如当月分区不存在，则新增当月分区
execute immediate 'alter table '||var_tab_name||' add partition '||var_partition||' values less than ('||var_less_than||')';
end if;
select count(1) into var_flag from user_segments
where segment_name = var_tab_bak_name
and partition_name like '%'||to_char(add_months(sysdate , -4) , 'yyyy_mm') ;
if var_flag = 0 then
select var_tab_bak_name||'_'||to_char(add_months(sysdate , -4) , 'yyyy_mm') into var_partition from dual ;
select to_char(add_months(sysdate , -3) , 'yyyymm')||'01' into var_less_than from dual ;
--如备份表分区不存在，则新增备份月分区
execute immediate 'alter table '||var_tab_bak_name||' add partition '||var_partition||' values less than ('||var_less_than||')';
--备份数据
select var_tab_name||'_'||to_char(add_months(sysdate , -4) , 'yyyy_mm') into var_partition from dual ;
execute immediate 'insert into '||var_tab_bak_name||' select * from '||var_tab_name||' partition('||var_partition||')';
/*insert into var_tab_bak_name
select * from var_tab_name partition(var_partition) ;*/
commit ;
--删除已备份月分区
execute immediate 'alter table '||var_tab_name||' drop partition '||var_partition;
end if;
end;
