ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 转:自动添加分区脚本

转:自动添加分区脚本

原创 Linux操作系统 作者:dppass2 时间:2011-05-31 15:43:33 0 删除 编辑
平台如下:
os:centos4.7
oracle 10g
定时添加或删除分区表的分区
这几天,根据业务的需求和性能的考虑,需要定时删除历史数据,表里
只保留最近指定的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-----
 
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wyzxg/archive/2009/04/15/4076996.aspx

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

请登录后发表评论 登录
全部评论

注册时间:2008-11-26

  • 博文量
    39
  • 访问量
    76435