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）;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址