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) < 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);