管理分区表（自动创建和删除子分区）

        我管理的数据库是一个数据仓库系统，数据量比较大，对一些系统ETL产生的中间表打算只保存半年，因此我设计了一个程序来帮助我管理我的分区表。他会自动创建子分区，并且把半年以前的子分区删除。

------------------------自动创建子分区的存储过程----------------------------

CREATE OR REPLACE PROCEDURE GENE_AUTO_ADD_PARTITION(part_month        varchar2)
IS

/***********************************************************************
/* Procedure   : GENE_AUTO_ADD_PARTITION
/* Description : General类程序，用于自动增加分区表的分区 == 计算到part_month为止，该增加多少个分区
/* Logic       : 1、找出需要增加分区的分区表，以及这些分区表的最近分区
/*               2、计算这些分区表要增加几个分区
/*               3、循环为这些分区表增加分区，直至增加到part_month分区为止
/*               4、记录日志
/* Warning     : 确保有足够权限，需显示授权：Grant select on dba_segments to user;
/* eg          : call gene_auto_add_partition(201210);
/* Author       YYYY-MM-DD      DSC
/* Feng         2012-07-30      初始版本
/*
/*********************************************************************/
  vcurrPartitionName                 varchar2(100);
  vhistPartitionName                 varchar2(100);
  vCount                             number;
  vDDLSql                            varchar2(1024);
  vLessthan                          varchar2(24);
  i1                                 number;
  i2                                 number;
 
BEGIN
 
  --format current partition name
  vcurrPartitionName := 'M_'||part_month;
 
  For x in (
    SELECT segment_name , max(partition_name) partition_name, max(tablespace_name) tablespace_name
      FROM dba_segments a
     WHERE wner = 'BAM'
       AND segment_type = 'TABLE PARTITION'
       AND segment_name not like '%BIN%'
       AND not exists (
          SELECT 1
            FROM dba_segments b
           WHERE a.segment_name = b.segment_name
             AND b.partition_name = vcurrPartitionName)
     GROUP BY a.segment_name
  ) LOOP
 
    --计算需要增加几个分区
    SELECT (extract(year from to_date(part_month,'yyyymm')) - extract(year from to_date(substr(x.partition_name,3),'yyyymm')))*12 +
           (extract(month from to_date(part_month,'yyyymm')) - extract(month from to_date(substr(x.partition_name,3),'yyyymm')))
      INTO vCount
      FROM dual;
   
    For j in 1.. vCount LOOP
     
      --get history Partition name
      vhistPartitionName := 'M_'||to_char(add_months(to_date(substr(x.partition_name,3),'yyyymm'),j),'yyyymm');
      vLessthan := to_char(add_months(to_date(substr(x.partition_name,3),'yyyymm'),j),'yyyymmdd');
     
      vDDLSql := 'ALTER TABLE '||x.segment_name||' ADD PARTITION '||vhistPartitionName||' VALUES LESS THAN('||vLessthan||') TABLESPACE '||x.tablespace_name;
     
      DBMS_UTILITY.exec_ddl_statement(vDDLSql);
     
      i1:= i1 + 1;
     
    END LOOP;
   
    i1 := i1;
    i2 := i2 + 1;
  
  END LOOP;
 
  raise_log('GENE_AUTO_ADD_PARTITION','为'||part_month||'创建子分区成功，共创建了'||i2||'张表的'||i1||'个分区');
 
EXCEPTION
  WHEN others THEN
    raise_application_err(SQLCODE,'GENE_AUTO_ADD_PARTITION',SQLERRM);
END;

------------------------自动删除子分区的存储过程----------------------------

CREATE OR REPLACE PROCEDURE GENE_AUTO_DROP_PARTITION(part_month      varchar2
                                                    ,period          number default 6)
IS

/***********************************************************************
/* Procedure   : GENE_AUTO_DROP_PARTITION
/* Description : General类程序，用于自动删除分区表的分区
/* Logic       : 1、找出需要删除分区的分区表，以及这些分区表的最远分区,确保只删除bam_cur_dat, bam_plf_dat表空间中的分区表
/*               2、以period为界限，计算这些分区表要删除几个分区
/*               3、循环为这些分区表删除分区，直至删除到part_month分区为止
/*               4、检查表索引的状态，对于失效的索引进行重新分析。
/*               5、记录日志
/* Warning     : 确保有足够权限，需显示授权：Grant select on dba_segments to user;
/*               分区索引(local)不存在删除索引后索引失效的问题，因此在此不考虑索引失效的rebuild问题。
/*               由于数据仓库事实表中极少用到主外键约束，因此在此不考虑暂时停用约束和启用约束的问题。
/*               所删除的分区采用直接丢弃方式，因此在此不考虑删除分区前，对分区的数据进行备份。
/* eg          : call gene_auto_drop_partition(201210);
/*               call gene_auto_drop_partition(201210,12);
/* Author       YYYY-MM-DD      DSC
/* Feng         2012-07-31      初始版本
/*
/*********************************************************************/

  vPartMonth                             varchar2(100);
  vOldPartitionName                      varchar2(100);
  vhistPartitionName                     varchar2(100);
  vCount                                 number;
  vDDLSql                                varchar2(1024);
  i                                      number;
  i2                                     number;
 
BEGIN
 
  --format Old partition name
  vPartMonth := to_char(add_months(to_date(part_month,'yyyymm'),-period),'yyyymm');
  vOldPartitionName := 'M_'||vPartMonth;
  dbms_output.put_line(vOldPartitionName);
 
  i := 0;
  i2:= 0;
 
  For x in (
    SELECT segment_name , min(partition_name) partition_name, min(tablespace_name) tablespace_name
        FROM dba_segments a
       WHERE wner = 'BAM'
         AND segment_type = 'TABLE PARTITION'
         AND segment_name not like '%BIN%'
         AND tablespace_name = any('BAM_CUR_DAT','BAM_PLF_DAT')
         AND exists (
            SELECT 1
              FROM dba_segments b
             WHERE a.segment_name = b.segment_name
               AND b.partition_name = vOldPartitionName)
       GROUP BY a.segment_name
  ) LOOP
 
    --计算要删除几个分区
    SELECT (extract(year from to_date(vPartMonth,'yyyymm')) - extract(year from to_date(substr(x.partition_name,3),'yyyymm')))*12 +
           (extract(month from to_date(vPartMonth,'yyyymm')) - extract(month from to_date(substr(x.partition_name,3),'yyyymm')))
      INTO vCount
      FROM dual;
 
    For j in 0.. vCount LOOP
     
      --get history partition name
      vhistPartitionName := 'M_'||to_char(add_months(to_date(substr(x.partition_name,3),'yyyymm'),j),'yyyymm');
     
      vDDLSql := 'ALTER TABLE '||x.segment_name||' DROP PARTITION '||vhistPartitionName;
     
      i := i + 1;
     
      raise_log('GENE_AUTO_DROP_PARTITION','删除'||x.segment_name||'表分区成功!删除了'||vhistPartitionName);
     
      DBMS_UTILITY.exec_ddl_statement(vDDLSql);
     
    END LOOP;
   
    i := i ;
    i2 := i2 + 1;
   
  END LOOP;
 
  raise_log('GENE_AUTO_DROP_PARTITION','删除'||vPartMonth||'之前的分区成功，删除了'||i2||'张表的'||i||'个分区。');
 
EXCEPTION
  WHEN others THEN
    raise_application_err(SQLCODE,'GENE_AUTO_DROP_PARTITION',SQLERRM);
END GENE_AUTO_DROP_PARTITION;

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

转载于:http://blog.itpub.net/10356975/viewspace-739345/