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) < to_char(sysdate - interval '6' day,'yyyymmdd');
--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) < to_char(sysdate - interval '1' year,'yyyymmdd');
--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);