oracle定时添加或删除分区表的分区

这几天，根据业务的需求和性能的考虑，需要定时删除历史数据，表里
只保留最近指定的wareid的数据，考虑删除时对表的影响及便利性，我决定
把这个表建立成分区表，定期添加和删除分区

步骤：


1. 创建相关procedure
    pro_ADD_PARTITION_wareid    ---用户添加分区的过程
    pro_DROP_PARTITION_wareid   ---用户删除分区的过程
    pro_expdatainto_his         ---用户备份要删除分区数据的过程


2.创建系统上的脚本


3.用cron做定时任务


1. 创建相关procedure


CREATE OR REPLACE PROCEDURE pro_ADD_PARTITION_wareid(tablename      varchar2, ---要添加分区的表
                                                     partNum        NUMBER, --添加分区的个数
                                                     TableSpaceName VARCHAR2, --表空间名
                                                     wareidnum      number default 100 ---分区的范围
                                                     ) AS
  /*******************************************************
  author：skate
  time  ：2009/02/28
  功能：添加分区表的指定分区
  说明：可以通过定时任务来完成自动添加分区，添加后要检查索引的状态
        最好是重建索引，以达到对表的分析
  eg：  exec pro_add_partition_wareid('d_order_bak',2,'yytickets','200');
  ********************************************************/

  v_SqlExec     VARCHAR2(2000); --DDL语句变量
  v_Partwareid1 number; --创建分区的wareid
  v_err_num     NUMBER; --ORA错误号
  v_err_msg     VARCHAR2(100); --错误描述

  v_part_wareid_max number; --tablename 表分区的最大wareid号
  v_begin           number; ----字符串的开始位置
  v_count           number; ----取多少个字符串
  v_part_name       varchar2(100); --要添加分区表的名称的前缀

BEGIN

  ----字符串的开始位置
  select INSTR(partition_name, '_', -1, 1) + 1
    into v_begin
    from user_tab_partitions
   where table_name = UPPER(tablename)
     and rownum < 2;

  ----取多少个字符串
  select length(partition_name) - v_begin + 1
    into v_count
    from user_tab_partitions
   where table_name = UPPER(tablename)
     and rownum < 2;

  --查询分区表tablename的最大wareid值
  select max(to_number(SUBSTR(partition_name, v_begin, v_count)))
    into v_part_wareid_max
    from user_tab_partitions
   WHERE table_name = UPPER(tablename);

  ---计算分区表的名称
  select SUBSTR(partition_name, 1, v_begin - 1)
    into v_part_name
    from user_tab_partitions
   WHERE table_name = UPPER(tablename)
     and rownum < 2;

  v_Partwareid1 := v_part_wareid_max;

  FOR i IN 1 .. partNum LOOP

    v_Partwareid1 := v_Partwareid1 + wareidnum; ----计算要添加分区的wareid

   
   
    v_SqlExec := 'ALTER TABLE ' || tablename || ' ADD PARTITION ' ||
                 v_part_name || v_Partwareid1 || ' values less than(' ||
                 v_Partwareid1 || ') TABLESPACE ' || TableSpaceName;
    dbms_output.put_line('创建 d_order 表分区' || i || '=' || v_SqlExec);
    DBMS_Utility.Exec_DDL_Statement(v_SqlExec);

  END LOOP;
  /*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 pro_ADD_PARTITION_wareid;

##########################################################################################

删除分区的procedure：

CREATE OR REPLACE PROCEDURE pro_DROP_PARTITION_wareid(tablename    varchar2, ---要删除分区表的名称
                                                      beforewareid NUMBER default 200 --要保留最新多少wareid
                                                      ) As

  /*******************************************************
  author：skate
  time  ：2009/02/28
  功能：删除分区表的指定分区
  说明：可以通过定时任务来完成自动删除分区，删除后要检查索引的状态
        最好是重建索引，以达到对表的分析

  eg：exec pro_drop_partition_wareid('d_order_bak',10800);
  ********************************************************/

  v_SqlExec VARCHAR2(2000); --DDL语句变量
  v_err_num NUMBER; --ORA错误号
  v_err_msg VARCHAR2(100); --错误描述

  v_begin       number; ----字符串的开始位置
  v_count       number; ----取多少个字符串
  v_ware_id     number;
  v_max_ware_id number;
  b  varchar2(10);
 
  cursor cursor_table_part is
    select partition_name
      from user_tab_partitions
     WHERE table_name = UPPER(tablename)
       AND SUBSTR(partition_name, v_begin, v_count) < v_ware_id
     ORDER BY partition_name;
---为禁用或启用约束而定义的游标
    cursor cursor_const_part is
    select b.constraint_name
      from user_constraints a, user_cons_columns b
      where a.table_name=b.table_name
        and   a.constraint_name=b.constraint_name
        and   a.owner='TICKETS'
        and   a.table_name=upper(tablename);
       
    ---为索引失效而重建索引定义游标
    cursor cursor_idx is
    select index_name
      from user_indexes
     where table_name = UPPER(tablename)
       and status = 'UNUSABLE';
  

  record_table_oldpart cursor_table_part%rowType;
  record_cursor_const_part cursor_const_part%rowtype;
  record_cursor_idx cursor_idx%rowtype;

BEGIN

  ----字符串的开始位置
  select INSTR(partition_name, '_', -1, 1) + 1
    into v_begin
    from user_tab_partitions
   where table_name = UPPER(tablename)
     and rownum < 2;

  ----取多少个字符串
  select length(partition_name) - v_begin + 1
    into v_count
    from user_tab_partitions
   where table_name = UPPER(tablename)
     and rownum < 2;

  --确定要删除的最大wareid
  select max(to_number(substr(partition_name, v_begin, v_count)))
    into v_max_ware_id
    from user_tab_partitions
    where table_name = UPPER(tablename);

  --select max(w.ware_id) into v_max_ware_id from d_ware w;
  v_ware_id := v_max_ware_id - beforewareid;

    ---暂时禁用约束
    open cursor_const_part;
    loop
      fetch cursor_const_part into record_cursor_const_part;
      exit when cursor_const_part%notfound;
   
      execute immediate 'alter table '||tablename||' disable constraint '||record_cursor_const_part.constraint_name||' cascade';
   
    end loop; 
    close cursor_const_part;

  open cursor_table_part;
  loop
    fetch cursor_table_part
      into record_table_oldpart;
    exit when cursor_table_part%notfound;

    if substr(record_table_oldpart.partition_name, v_begin, v_count) <
       v_ware_id then
      --把要删除的数据提前备份到历史表里
      pro_expdatainto_his(tablename,record_table_oldpart.partition_name,b);
     
      --验证是否可以删除指定的分区表
      if (b='Y' or b='R') then
     
       
         --删除 tablename 表分区
         
          v_SqlExec := 'ALTER TABLE ' || tablename || ' DROP PARTITION ' ||
                       record_table_oldpart.partition_name;
          dbms_output.put_line('删除' || tablename || '表分区=' || v_SqlExec);
          DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
         
       
         
      elsif b='N' then
      exit;
      end if;
        
    end if;
  end loop;
  close cursor_table_part;
 
      ---启用约束
       open cursor_const_part;
         loop
          fetch cursor_const_part into record_cursor_const_part;
          exit when cursor_const_part%notfound;
         
           execute immediate 'alter table '||tablename||' enable novalidate constraint '||record_cursor_const_part.constraint_name;
         
         end loop; 
         close cursor_const_part;
        
     --重建失效的索引
        open cursor_idx;
        loop
          fetch cursor_idx into record_cursor_idx;
          exit when cursor_idx%notfound;
         
          execute immediate 'alter index ' ||record_cursor_idx.index_name||' rebuild';
        
         end loop;
         close  cursor_idx;

  /*EXCEPTION
  WHEN OTHERS THEN
    v_err_num := SQLCODE;
    v_err_msg := SUBSTR(SQLERRM, 1, 100);
    dbms_output.put_line(pro_DROP_PARTITION_wareid ||
                         '执行出现异常,错误码=' || v_err_num ||
                         '错误描述=' || v_err_msg);*/


END pro_DROP_PARTITION_wareid;


#########################################################################################

这个存储过程用户在删除分区的时候，用户备份所删除的分区表


create or replace procedure pro_expdatainto_his(tablename      varchar2,---要导数据的表名
                                                part_tablename varchar2,---要导数据的分区表名
                                                issuccess      in out varchar2)--返回是否导入成功 （Y：成功 N：失败 R：之前已经导入过了）
                                                 as

  v_SqlExechis VARCHAR2(2000); --DDL语句变量
  v_SqlExec    VARCHAR2(2000); --DDL语句变量
  v_count      number(30);     --本次导入的数据行数
  v_expcount   number(30);     --历史表中有多少符合本次导入的行数
  /*******************************************************
  author：skate
  time  ：2009/04/15
  功能：把预删除的分区表的数据提前导入到历史表中
  说明：这个程序是配合定时删除分区的过程，pro_add_partition_wareid调用
        此过程，本过程适合如下表：
          D_ORDER
          D_ORDER_DETAIL
          D_PRINT_BATCH
          d_batch_detail
        
         如果想让其适合其他表，只要做简单修改即可
         
  eg：  exec pro_expdatainto_his('d_order','D_ORDER_P_31100',b);
 
  注意：本procedure的表名用到了变量，所以要用动态sql来执行：execute immediate
  ********************************************************/ 
 
begin


  if upper(tablename) = 'D_ORDER' then
 
    v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
                 part_tablename || ')';
    --准备导入多少行数据            
    execute immediate v_SqlExec
      into v_count;
   --执行导入操作
    execute immediate 'insert into ' || tablename ||
                      '_his  select * from '||tablename||' partition(' ||
                      part_tablename || ') nologing';
 
    v_SqlExechis := 'select count(1)  from ' || tablename || '_his th ,' ||
                    tablename || ' partition(' || part_tablename ||
                    ') t where t.order_id=th.order_id';
 
   --在历史表中有多少和本此导入相同的记录
    execute immediate v_SqlExechis
      into v_expcount;
 
  elsif upper(tablename) = 'D_ORDER_DETAIL' then
 
    v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
                 part_tablename || ')';
    execute immediate v_SqlExec
      into v_count;
 
    execute immediate 'insert into ' || tablename ||'_his  select * from '||tablename||' partition('||part_tablename || ') nologing';
 
    v_SqlExechis := 'select count(1)  from ' || tablename || '_his th ,' ||tablename || ' partition(' || part_tablename ||') t where t.detail_id=th.detail_id';
 
    execute immediate v_SqlExechis
      into v_expcount;
 
  elsif upper(tablename) = 'D_PRINT_BATCH' then
 
    v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
                 part_tablename || ')';
    execute immediate v_SqlExec
      into v_count;
 
    execute immediate 'insert into ' || tablename ||
                      '_his  select * from '||tablename||' partition(' ||
                      part_tablename || ') nologing';
 
    v_SqlExechis := 'select count(1)  from ' || tablename || '_his th ,' ||
                    tablename || ' partition(' || part_tablename ||
                    ') t where t.batch_id=th.batch_id';
 
    execute immediate v_SqlExechis
      into v_expcount;
 
  elsif upper(tablename) = upper('d_batch_detail') then
 
    v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
                 part_tablename || ')';
    execute immediate v_SqlExec
      into v_count;
 
    execute immediate 'insert into ' || tablename ||
                      '_his  select * from '||tablename||' partition(' ||
                      part_tablename || ') nologing';
 
    v_SqlExechis := 'select count(1)  from ' || tablename || '_his th ,' ||
                    tablename || ' partition(' || part_tablename ||
                    ') t where t.batch_detail_id=th.batch_detail_id';
 
    execute immediate v_SqlExechis
      into v_expcount;
 
  end if;

--验证是否导入成功，如果成功就commit，否则rollback
  if v_count = v_expcount then
    issuccess := 'Y';
    commit;
  elsif v_count * 2 = v_expcount then
    issuccess := 'R';
    rollback;
  else
    issuccess := 'N';
    rollback;
  end if;

end pro_expdatainto_his;

################################################################

2.创建系统上的脚本

[oracle@svr-db-test sh]$ more ticket_del_part.sh
cd /home/oracle/sh
date
sqlplus /nolog @ ticket_del_part.sql
date
[oracle@svr-db-test sh]$


[oracle@svr-db-test sh]$ more ticket_del_part.sql
connect tickets/123456
set timing on
exec  pro_drop_partition_wareid('d_order',50);
exec  pro_drop_partition_wareid('d_order_detail',50);
exec  pro_drop_partition_wareid('D_PRINT_BATCH',50);

exit
[oracle@svr-db-test sh]$

3.用cron做定时任务

在oracle用户的crontab下增加一条定时任务即可：


#delete partition

1 */3 * * * sh /home/oracle/sh/ticket_del_part.sh >> /home/oracle/sh/ticket_del_part.log


简单吧！！！


真TMD累啊！！ 回家


------end-----