注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

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

2010-09-06 17:49:49|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

引用

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

平台如下:

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


简单吧!!!

 

本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wyzxg/archive/2009/04/15/4076996.aspx

参考:oracle定时添加或删除分区表的分区 存儲過程 增刪分區 操作分區表, 包 pl/sql

  评论这张
 
阅读(874)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016