oracle分区表自动维护

前几天因为工作需要，所以在网上查到分区表自动维护资料。

在 http://blog.csdn.net/rulev5/article/details/8505955中找到了对于时间分区表的自动维护脚本，但是工作需要对时间和字符分区都进行维护，于是对脚本进行了改进。

本脚本主要由2个部分组成：sys_ConfigTable.sql、sys_pro_AddAndDropPartition.sql
1、sys_ConfigTable.sql 主要创建了一张配置表：这里填写具体要自动维护的表名、保存天数、每天分区的个数等等；

2、sys_pro_AddAndDropPartition.sql 这个用来自动增加表分区，删除过期分区；

3、sys_pr_mergetableandrebuildindex.sql这个用户重建配置表中的表的失效的全局索引

1、sys_ConfigTable.sql

CREATE TABLE TB_PARTITION_CONFIGTABLE
(
  NAME    VARCHAR2(64 BYTE)                     NOT NULL,
  VALUE   VARCHAR2(64 BYTE)                     NOT NULL,
  TYPE    VARCHAR2(64 BYTE)                     NOT NULL,
  ISRUN   NUMBER(1)                             NOT NULL,
  REMARK  VARCHAR2(64 BYTE)
);

COMMENT ON TABLE TB_PARTITION_CONFIGTABLE IS '分区自动维护配置表';

COMMENT ON COLUMN TB_PARTITION_CONFIGTABLE.NAME IS '表名';

COMMENT ON COLUMN TB_PARTITION_CONFIGTABLE.VALUE IS '值';

COMMENT ON COLUMN TB_PARTITION_CONFIGTABLE.TYPE IS 'protocal：日志表，save：保存，num_part_byday：添加分区,category:分区类别';

COMMENT ON COLUMN TB_PARTITION_CONFIGTABLE.ISRUN IS '1：使用，0：不使用';

COMMENT ON COLUMN TB_PARTITION_CONFIGTABLE.REMARK IS '备注';

Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_IF_LOG', 'TL_IF_LOG', 'protocal', 1, '接口日志表');
Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_IF_LOG', '7', 'save', 1, '日志保存天数');
Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_IF_LOG', '1', 'num_part_byday', 1, '每天要添加的分区个数');
Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_IF_LOG', 'date', 'category', 1, '时间分区');

Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_ITV_DATA_LOG', 'TL_ITV_DATA_LOG', 'protocal', 1, 'PV日志表');
Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_ITV_DATA_LOG', '30', 'save', 1, '日志保存天数');
Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_ITV_DATA_LOG', '1', 'num_part_byday', 1, '每天要添加的分区个数');
Insert into TB_PARTITION_CONFIGTABLE
   (NAME, VALUE, TYPE, ISRUN, REMARK)
 Values
   ('TL_ITV_DATA_LOG', 'char', 'category', 1, '字符分区');

   2、sys_pro_AddAndDropPartition.sql

 

 CREATE OR REPLACE PROCEDURE PR_AddAndDropPartition AS  
  /*******************************************************  
  功能：添加分区表的指定分区  
  示例： exec sys_pro_AddAndDropPartition;  
  说明：协议、表名称、保存天数从配置表TB_PARTITION_CONFIGTABLE读取  
  ********************************************************/  
  v_sql_gettablename       long; --取协议对于的表名sql  
  v_sql_gettablespacename  long; --取表对应的表空间sql  
  v_tablename              dbms_sql.Varchar2_Table; --协议对于的表名  
  v_tableSpaceName         varchar2(32); --表对应的表空间  
  v_sql_get_partition_name long;  
  v_del_partition_name     dbms_sql.Varchar2_Table;  
  v_SqlExec                VARCHAR2(2000); --DDL语句变量  
  v_SqlDel                 VARCHAR2(2000); --删除表分区  
  v_Partwareid1            NUMBER; --创建分区的wareid
  v_Partwareid2            VARCHAR2(20); --创建分区的字符
  v_err_num                NUMBER; --ORA错误号  
  v_err_msg                VARCHAR2(100); --错误描述  
  partNum                  NUMBER; --添加分区的个数  
  interval_day             NUMBER;  
  v_sql_getday             long;  
  v_saveday                varchar2(32);  
  v_part_wareid_nim        NUMBER; --v_tablename 表分区的最小wareid号  
  v_part_wareid_max        NUMBER; --v_tablename 表分区的最大wareid号  
  v_begin                  NUMBER; ----字符串的开始位置  
  v_end                    NUMBER; --字符串结束的位置   
  v_part_name              VARCHAR2(100); --要添加分区表的名称的前缀  
  num_part_byday           int;--每天要添加的分区个数，从TB_PARTITION_TB_PARTITION_CONFIGTABLE中读取
  partType           varchar2(5);--分区类型（是字符串分区还是时间分区），从TB_PARTITION_TB_PARTITION_CONFIGTABLE中读取
  v_num_part_byday         int;  
BEGIN  
  --最外层，对每个协议循环  
  v_sql_gettablename := 'select value from TB_PARTITION_CONFIGTABLE where type=''protocal'' and isrun=1';  
  execute immediate v_sql_gettablename bulk collect  
    into v_tablename;  
  for k in 1 .. v_tablename.count loop  
    v_sql_gettablespacename := 'select distinct TABLESPACE_NAME  from user_tab_partitions  where table_name = ''' ||  
                               v_tablename(k) || '''';  
    dbms_output.put_line(v_sql_gettablespacename);  
    execute immediate v_sql_gettablespacename  
      into v_tableSpaceName;  
    dbms_output.put_line('表空间为' || v_tableSpaceName);  
 
    --字符串的开始位置  
    select INSTR(partition_name, '_', 1, 1) + 1  
      into v_begin  
      from user_tab_partitions  
     where table_name = UPPER(v_tablename(k))  
       and rownum < 2;  
    dbms_output.put_line('字符串的开始位置 ' || v_begin);   
    --查询分区表tablename的最大wareid值，SUBSTR(partition_name, v_begin, v_count)表示分区名后面的分区数  
    select max(to_number(SUBSTR(partition_name, v_begin)))  
      into v_part_wareid_max  
      from user_tab_partitions  
     WHERE table_name = UPPER(v_tablename(k));  
    dbms_output.put_line('最大分区名称' || v_part_wareid_max);  
    --查询分区表tablename的最小wareid值，删除分区使用  
    select min(to_number(SUBSTR(partition_name, v_begin)))  
      into v_part_wareid_nim  
      from user_tab_partitions  
     WHERE table_name = UPPER(v_tablename(k));  
    dbms_output.put_line('最小分区名称' || v_part_wareid_nim);  
    --计算分区表的名称  
    select SUBSTR(partition_name, 1, v_begin - 1)  
      into v_part_name  
      from user_tab_partitions  
     WHERE table_name = UPPER(v_tablename(k))  
       and rownum < 2;  
    dbms_output.put_line('分区开头为' || v_part_name);  
    v_Partwareid1 := v_part_wareid_max;  
  dbms_output.put_line('最大分区为' || v_Partwareid1 );  
    partNum       := (trunc(sysdate) - to_date(v_part_wareid_max,'yyyymmdd')) + 1;  
    dbms_output.put_line('应该新建' || partNum || '天的数据');  
    --如果表未建好，执行建表语句  
  if partNum > 0 then  
      --新加分区表  
      FOR i IN 1 .. partNum LOOP  
        dbms_output.put_line('v_Partwareid1:' || v_Partwareid1);  
        select to_number(to_char((to_date(v_Partwareid1, 'yyyymmdd') + 1),  
                                 'yyyymmdd'))  
          into v_Partwareid1  
          from dual;  
        dbms_output.put_line(v_Partwareid1); --20111125  
        select b.value into num_part_byday from TB_PARTITION_CONFIGTABLE a, TB_PARTITION_CONFIGTABLE b where a.name = b.name and b.type = 'num_part_byday'  
           and a.value = UPPER(v_tablename(k)) and a.type = 'protocal' and a.isrun = 1;  
    
    select b.value into partType from TB_PARTITION_CONFIGTABLE a, TB_PARTITION_CONFIGTABLE b where a.name = b.name and b.type = 'category'  
           and a.value = UPPER(v_tablename(k)) and a.type = 'protocal' and a.isrun = 1;  
        if num_part_byday=0 or num_part_byday is null  then  
           num_part_byday:=1;  
        end if;  
        v_num_part_byday:=0;  
        loop   
          v_num_part_byday := v_num_part_byday + 24/num_part_byday;  

    if partType='date' then  
           v_SqlExec := 'ALTER TABLE ' || v_tablename(k) ||  
                       ' ADD PARTITION ' || v_part_name || v_Partwareid1 ||   
                       ' values less than(to_date( ''' ||    
                        v_Partwareid1 ||
                       '235959'',''YYYYMMDDhh24miss'')) ';
        elsif partType='char' then
        select to_char(to_date(v_Partwareid1, 'yyyymmdd'),  
                                 'yyyy-mm-dd')  
          into v_Partwareid2  
          from dual;
            v_SqlExec := 'ALTER TABLE ' || v_tablename(k) ||  
                       ' ADD PARTITION ' || v_part_name || v_Partwareid1 ||   
                       ' values less than( ''' ||    
                        v_Partwareid2 ||
                       ''' ) ';

        end if;     
                    dbms_output.put_line('创建表分区' || i || '=' || v_SqlExec);  
                    dbms_output.put_line(v_SqlExec);  
          DBMS_Utility.Exec_DDL_Statement(v_SqlExec);  
          exit when v_num_part_byday >= 24;  
        end loop;  
      END LOOP;  
    end if;  
    --删除过期的分区表  
 
    --查询分区表tablename的最大wareid值，SUBSTR(partition_name, v_begin, v_count)表示分区名后面的分区数  
    select max(to_number(SUBSTR(partition_name, v_begin)))  
      into v_part_wareid_max  
      from user_tab_partitions  
     WHERE table_name = UPPER(v_tablename(k));  
    dbms_output.put_line('最大分区名称' || v_part_wareid_max);  
    --查询分区表tablename的最小wareid值，删除分区使用  
    select min(to_number(SUBSTR(partition_name, v_begin)))  
      into v_part_wareid_nim  
      from user_tab_partitions  
     WHERE table_name = UPPER(v_tablename(k));  
    dbms_output.put_line('最小分区名称' || v_part_wareid_nim);  
    select (to_date(v_part_wareid_max, 'YYYYMMDD') -  
           to_date(v_part_wareid_nim, 'YYYYMMDD'))  
      into interval_day  
      from dual;  
    dbms_output.put_line('已经创建分区天数为' || interval_day);  
    select b.value  
      into v_saveday  
      from TB_PARTITION_CONFIGTABLE a, TB_PARTITION_CONFIGTABLE b  
     where a.name = b.name  
       and b.type = 'save'  
       and a.value = v_tablename(k);  
    dbms_output.put_line('数据保存天数为' || v_saveday);  
    if interval_day > v_saveday then  
      for j in 1 .. (interval_day - v_saveday) loop  
        v_sql_get_partition_name := 'select partition_name from user_tab_partitions where table_name= ''' ||  
                                    v_tablename(k) ||  
                                    ''' and partition_name like ''%' ||  
                                    v_part_wareid_nim || '%''';  
        --        execute immediate v_sql_get_partition_name bulk collect  
        --          into v_del_partition_name;  
        dbms_output.put_line(v_sql_get_partition_name);  
        execute immediate v_sql_get_partition_name bulk collect  
          into v_del_partition_name;  
        for l in 1 .. v_del_partition_name.count loop  
          v_SqlDel := 'ALTER TABLE ' || v_tablename(k) ||  
                      ' DROP PARTITION ' || v_del_partition_name(l);  
          dbms_output.put_line(v_SqlDel);  
          execute immediate (v_SqlDel);  
        end loop;  
        v_part_wareid_nim := v_part_wareid_nim + 1;  
        dbms_output.put_line('已删除' || v_part_wareid_nim);  
      end loop;  
    end if;  
  end loop;  
  commit;
  /*  EXCEPTION  
  WHEN OTHERS THEN  
      v_err_num := SQLCODE;  
      v_err_msg := SUBSTR(SQLERRM, 1, 100);  
      dbms_output.put_line('pro_ADD_PARTITION_wareid执行出现异常,错误码=' ||  
                           v_err_num || '错误描述=' || v_err_msg);  
  commit;*/  
END PR_AddAndDropPartition;
/

   3、sys_pr_mergetableandrebuildindex.sql

CREATE OR REPLACE procedure OPENAPI.PR_MERGETABLEANDREBUILDINDEX is  
  --数据文件合并在表空间的名称  
  /*******************************************************  
  功能：合并分区表  
  示例： exec PR_MERGETABLEANDREBUILDINDEX;  
  表空间、表通过TB_PARTITION_CONFIGTABLE读取配置  
  ********************************************************/     
  v_SqlInd          long; --查找失效的索引sql  
  v_Ind             dbms_sql.Varchar2_Table; --存放失效的索引  
  v_SqlInd2         long; --重建索引sql    
  --新增部分  
  v_protocal         varchar2(32);  
  v_sql_gettablename long;  
  v_tablename        dbms_sql.Varchar2_Table;   
begin  
  --最外层循环，根据协议取出表和表空间  
  --取出表  
    v_sql_gettablename := 'select value from TB_PARTITION_CONFIGTABLE where type=''protocal'' and isrun=1';  
  execute immediate v_sql_gettablename bulk collect  
    into v_tablename;  
      for l in 1 .. v_tablename.count loop  
    --取出表空间      
    --第二个内层循环重建失效的索引   
    --查询出失效的索引  
    select NAME into v_protocal from TB_PARTITION_CONFIGTABLE where TYPE='protocal' and VALUE=v_tablename(l);  
    v_SqlInd := 'select distinct index_name  
      from user_indexes  
     where  status = ''UNUSABLE'' and table_name = ''' ||  v_protocal || '''';  
    dbms_output.put_line(v_SqlInd);  
    --把失效索引名称赋值给v_Ind  
    execute immediate v_SqlInd bulk collect  
      into v_Ind;    
    dbms_output.put_line(v_Ind.count);
    --把全局索引重建  
    for k in 1 .. v_Ind.count loop  
      v_SqlInd2 := 'alter index ' || v_Ind(k) || ' rebuild ';  
      dbms_output.put_line(v_SqlInd2);  
       execute immediate v_SqlInd2;  
    end loop;  
  end loop;  
end;
/