分区管理过程

一次性添加一年分区
保留当前表20天数据，20天后数据事实被交换到历史表
保留分区表2个月数据
 
 
 
 

create or replace procedure manage_partition is

partition_name_add      varchar2(2000);
partition_name_reduce_5   varchar2(200);
current_time              varchar2(2000);
v_Sql                     varchar2(1000);
partiton_name             varchar2(5000);
partition_values          varchar2(2000);
swap_count                number(38);
pro_name                  varchar2(20000);
err_info                  varchar2(2000);
sj                        varchar2(2000);
new_day                   VARCHAR2(2000);
to_day                    varchar2(2000);
add_day                    varchar2(2000);
JL                         NUMBER;

cursor all_data is select table_name,SUBSTR(max(partition_name),6,9) as partition_name,tablespace_name from user_tab_partitions where  table_name in('TDL_PM_CELL_WEIFAQI_WEILURU','TDL_PM_CELL_WEIFAQI_WEILURU_H','TDL_PARA_CELL_LOCAL','TDL_PM_CELL_LOCAL','TDL_CM_CELL_LOCAL','TDL_CM_ENODEB_LOCAL') 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;

 cursor swap_partition is select partition_name,table_name from user_tab_partitions  where table_name in('TDL_PM_CELL_WEIFAQI_WEILURU')  and substr(partition_name,6,12) < to_char(sysdate - interval '20' day,'yyyymmdd') and num_rows>0;

begin

 PRO_DATA_UPDATE_LOG('manage_partition', '开始', null,null);

--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 substr(to_char(sysdate+1,'yyyymmdd'),1,8)  into partition_name_add from dual;

  select case
             when (MOD(to_char(sysdate,'yyyy'), 4) = 0 and MOD(to_char(sysdate,'yyyy'), 100) != 0) or --（四年一闰，百年不闰），(四百年再闰)
                  (MOD(to_char(sysdate,'yyyy'), 400) = 0) then
              '366'
             else
              '365'
           end DAYS
     into new_day from dual;

  select new_day-to_char(sysdate,'ddd') into to_day from dual;

for all_table in all_data loop

  if partition_name_add > all_table.partition_name then

    for m in 1 .. to_day loop

    select 'PDAY_'||substr(to_char(sysdate +m,'yyyymmdd'),1,8) into add_day from dual;

     select to_char(sysdate+m+1,'yyyy-mm-dd')||' 00:00:00' into partition_values from dual;

  v_Sql := 'alter table '||all_table.table_name||' add partition '||add_day||' values less than(TO_DATE('||''''||partition_values||''''||','||'''YYYY-MM-DD HH24:MI:SS'''||')) tablespace '||all_table.tablespace_name||'';
  execute immediate v_Sql;
  end loop;
  end if;

end loop;

    declare
    cursor old_partition_1 is select partition_name,table_name,num_rows from user_tab_partitions  where table_name in('TDL_PM_CELL_WEIFAQI_WEILURU','TDL_PARA_CELL_LOCAL','TDL_PM_CELL_LOCAL','TDL_CM_CELL_LOCAL','TDL_CM_ENODEB_LOCAL')  and substr(partition_name,6,12) < to_char(sysdate - interval '2' Month,'yyyymmdd');
    --old_p_1 user_tab_partitions.partition_name%type;,
    begin
      for old_p_1 in old_partition_1 loop
        IF old_p_1.num_rows=0 OR old_p_1.num_rows IS  NULL and old_p_1.table_name='TDL_PM_CELL_WEIFAQI_WEILURU' THEN
      v_Sql := 'alter table '||old_p_1.table_name||' drop partition '||old_p_1.partition_name||'';
      execute immediate v_Sql;
       /* ELSIF  old_p_1.table_name='TDL_PM_CELL_WEIFAQI_WEILURU_H' THEN
            v_Sql := 'alter table '||old_p_1.table_name||' drop partition '||old_p_1.partition_name||'';
            execute immediate v_Sql;*/

        ELSIF old_p_1.table_name='TDL_PARA_CELL_LOCAL'OR old_p_1.table_name='TDL_PM_CELL_LOCAL' OR old_p_1.table_name='TDL_CM_CELL_LOCAL'OR old_p_1.table_name='TDL_CM_ENODEB_LOCAL' THEN
          v_Sql := 'alter table '||old_p_1.table_name||' drop partition '||old_p_1.partition_name||'';
          execute immediate v_Sql;

        END IF;

      end loop;
   end;

    declare
    cursor old_partition_2 is select partition_name,table_name,num_rows from user_tab_partitions  where table_name in('TDL_PM_CELL_LOCAL')  and substr(partition_name,6,12) < to_char(sysdate - interval '10' day,'yyyymmdd');
    --old_p_1 user_tab_partitions.partition_name%type;,
    begin
      for old_p_1 in old_partition_2 loop
        IF old_p_1.table_name='TDL_PM_CELL_LOCAL' THEN

          v_Sql := 'alter table '||old_p_1.table_name||' drop partition '||old_p_1.partition_name||'';
          execute immediate v_Sql;

        END IF;

      end loop;
   end;

  for n in swap_partition loop
    select count(1) into swap_count from T_PARTITION_SWAP_1;
    if swap_count=0 then

       v_Sql := 'alter table TDL_PM_CELL_WEIFAQI_WEILURU exchange partition '||n.partition_name||' with table T_PARTITION_SWAP_1 UPDATE INDEXES';
      execute immediate v_Sql;
       v_Sql := 'alter table TDL_PM_CELL_WEIFAQI_WEILURU_H exchange partition '||n.partition_name||' with table T_PARTITION_SWAP_1 UPDATE INDEXES';
       execute immediate v_Sql;
     else
       v_Sql := 'truncate table T_PARTITION_SWAP_1';
       execute immediate v_Sql;
           v_Sql := 'alter table TDL_PM_CELL_WEIFAQI_WEILURU exchange partition '||n.partition_name||' with table T_PARTITION_SWAP_1 UPDATE INDEXES';
      execute immediate v_Sql;
       v_Sql := 'alter table TDL_PM_CELL_WEIFAQI_WEILURU_H exchange partition '||n.partition_name||' with table T_PARTITION_SWAP_1 UPDATE INDEXES';
       execute immediate v_Sql;
     end if;
   end loop;

PRO_DATA_UPDATE_LOG('manage_partition', '结束', '正常',1);
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;*/
 PRO_DATA_UPDATE_LOG('manage_partition', sqlerrm||chr(10)||dbms_utility.format_error_backtrace, '异常',0);
    dbms_output.put_line(sqlcode||sqlerrm);
end manage_partition;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-2121446/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-2121446/