Oracle分区表数据迁移、管理自动化过程

下面过程围绕公司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);

本文永久更新链接地址：