下面过程围绕公司oracle数据库运行环境，写出来主要目的为了实现自动化。
下面过程围绕公司oracle数据库运行环境，写出来主要目的为了实现自动化。
过程完成任务有
1.自动添加前一天分区，以时间做分区
2.自动删除t_partition_1分区表6天前分区，t_partition_1是当前表
3.自动删除t_partition_2分区表1年前分区，t_partition_2是历史表又存放历史数据
4.只交换当5天前一天数据，把t_partition_1表里面数据交换到t_partition_swap，，在t_partition_swap交换到t_partition_2历史表
5.有异常会插入一张错误日志表，方便查看
过程名：manage_partition
create or replace procedure manage_partition is
partition_name_add_1 varchar2(20);
partition_name_reduce_5 varchar2(20);
current_time varchar2(20);
v_sql varchar2(1000);
partiton_name varchar2(50);
partition_values varchar2(20);
swap_count number(38);
pro_name varchar2(20);
err_info varchar2(20);
sj varchar2(20);
cursor all_data is select table_name,max(partition_name) as partition_name,tablespace_name from user_tab_partitions where table_name in('t_partition_1','t_partition_2') group by
table_name,tablespace_name;
type mt_his is record(table_name varchar2(20),partiton_name varchar2(20),tablespace_name varchar2(50));
all_table mt_his;
begin
select to_char(sysdate+1,'yyyy-mm-dd hh24:mi:ss') into partition_values from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into current_time from dual;
select 'p_'||substr(to_char(sysdate+1,'yyyymmdd'),1,8)||'_23' into partition_name_add_1 from dual;
select 'p_'||substr(to_char(sysdate - interval '5' day,'yyyymmdd'),1,8)||'_23' into partition_name_reduce_5 from dual;
for all_table in all_data loop
if partition_name_add_1 all_table.partition_name then
v_sql := 'alter table '||all_table.table_name||' add partition '||partition_name_add_1||' values less than(to_date('||''''||partition_values||''''||','||'''yyyy-mm-dd hh24:mi:ss'''||')) tablespace '||all_table.tablespace_name||'';
execute immediate v_sql;
end if;
end loop;
declare
cursor old_partition_1 is select partition_name,table_name from user_tab_partitions where table_name='t_partition_1' and substr(partition_name,3,10) --old_p_1 user_tab_partitions.partition_name%type;
begin
for old_p_1 in old_partition_1 loop
v_sql := 'alter table '||old_p_1.table_name||' drop partition '||old_p_1.partition_name||'';
execute immediate v_sql;
end loop;
end;
declare
cursor old_partition_2 is select partition_name,table_name from user_tab_partitions where table_name='t_partition_2' and substr(partition_name,3,10) --old_p_1 user_tab_partitions.partition_name%type;
begin
for old_p_2 in old_partition_2 loop
v_sql := 'alter table '||old_p_2.table_name||' drop partition '||old_p_2.partition_name||'';
dbms_output.put_line(old_p_2.table_name);
execute immediate v_sql;
end loop;
end;
select count(1) into swap_count from t_partition_swap;
if swap_count=0 then
v_sql := 'alter table t_partition_1 exchange partition '||partition_name_reduce_5||' with table t_partition_swap update indexes';
execute immediate v_sql;
v_sql := 'alter table t_partition_2 exchange partition '||partition_name_reduce_5||' with table t_partition_swap update indexes';
execute immediate v_sql;
else
v_sql := 'truncate table t_smsgateway_mt_swap';
execute immediate v_sql;
v_sql := 'alter table t_smsgateway_mt exchange partition '||partition_name_reduce_5||' with table t_smsgateway_mt_swap update indexes';
execute immediate v_sql;
v_sql := 'alter table t_smsgateway_mt_his exchange partition '||partition_name_reduce_5||' with table t_smsgateway_mt_swap update indexes';
execute immediate v_sql;
end if;
exception
when others then
--sg_log_err('manage_partition',sqlerrm);
pro_name :='manage_partition';
err_info :=sqlerrm;
select sysdate into sj from dual;
v_sql := 'insert into err_log values('||'''pro_name'''||','''||err_info||''','''||sj||''')';
execute immediate v_sql;
commit;
dbms_output.put_line(sqlcode||sqlerrm);
end manage_partition;
错误日志表用来记录异常日志
创建语句
create table err_log(pro_name varchar2(20),err_log varchar2(200),error_time date);
本文永久更新链接地址：