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

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

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

2010-09-14 17:55:32|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 

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

本定时创建的是以时间 做为分区表的分区字段 ,字段类型是timestamp

 

先创建包头:

 

 

Pl/sql代码
  1. create or replace package pkg_partition_alter is   
  2. /**   
  3. date:2010-09-14  
  4. author:wanggang   
  5. */   
  6.      
  7.   /**   
  8.     删除分区表中,指定时间段内的分区   
  9.     ####   
  10.     参数:   
  11.     v_table_name 分区表名   
  12.     v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含   
  13.     v_end_date 删除分区的结束时间 (null 不限定结束时间)   不包含   
  14.     ####   
  15.     使用注意:   
  16.     1.假定分区表中分区是按时间戳 timestamp 字段 来分区的   
  17.     2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的   
  18.     3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间   
  19.     4.只有最后一个分区时是无法删除的   
  20.     **/   
  21.   procedure   drop_partition(v_table_name in varchar2 ,   
  22.                                v_begin_date timestamp default null,    
  23.                                v_end_date timestamp default null);   
  24.      
  25.   /**   
  26.         根据分区信息,以及指定的时间,添加分区   
  27.     */   
  28.     procedure   add_partition(v_table_name in varchar2 ,   
  29.                               v_tablespace_name_in in varchar2 default null,   
  30.                                v_begin_date timestamp default null,    
  31.                                v_end_date timestamp);    
  32.   /**   
  33.         根据原来的最后分区信息,自动追加分区   
  34.     */   
  35.     procedure   append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,   
  36.                                v_end_date in timestamp);                             
  37. end;  
create or replace package pkg_partition_alter is  /**  date:2010-09-14  author:wanggang  */        /**      删除分区表中,指定时间段内的分区      ####      参数:      v_table_name 分区表名      v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含      v_end_date 删除分区的结束时间 (null 不限定结束时间)   不包含      ####      使用注意:      1.假定分区表中分区是按时间戳 timestamp 字段 来分区的      2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的      3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间      4.只有最后一个分区时是无法删除的      **/    procedure   drop_partition(v_table_name in varchar2 ,                                 v_begin_date timestamp default null,                                  v_end_date timestamp default null);        /**          根据分区信息,以及指定的时间,添加分区      */      procedure   add_partition(v_table_name in varchar2 ,                                v_tablespace_name_in in varchar2 default null,                                 v_begin_date timestamp default null,                                  v_end_date timestamp);     /**          根据原来的最后分区信息,自动追加分区      */      procedure   append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,                                 v_end_date in timestamp);                            end;  

 再创建包的实现部分

 

 

Pl/sql代码
  1. create or replace package body pkg_partition_alter is   
  2.   
  3.   
  4.      v_pos number := 11;--截断high_value中信息的字符部分起始位   
  5.        
  6.     /**   
  7.     删除分区表中,指定时间段内的分区   
  8.     ####   
  9.     参数:   
  10.     v_table_name 分区表名   
  11.     v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含   
  12.     v_end_date 删除分区的结束时间 (null 不限定结束时间)   不包含   
  13.     ####   
  14.     使用注意:   
  15.     1.假定分区表中分区是按时间戳 timestamp 字段 来分区的   
  16.     2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的   
  17.     3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间   
  18.     4.只有最后一个分区时是无法删除的   
  19.     **/   
  20.     procedure drop_partition(   
  21.     v_table_name in varchar2 ,   
  22.     v_begin_date timestamp default null,   
  23.     v_end_date timestamp default null   
  24.     )   
  25.     is   
  26.       ---分区表信息   
  27.       cursor cur_utp(   
  28.         v_table_name in user_tab_partitions.table_name%TYPE   
  29.       )   
  30.       is   
  31.       select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp   
  32.       where utp.table_name = UPPER(v_table_name)   
  33.       order by utp.partition_position ;   
  34.       v_high_value varchar2(255);--less than value信息   
  35.       v_partition_max_date timestamp;----less than value信息的 timestamp表示形式   
  36.       v_sqlexec VARCHAR2(2000); --DDL语句变量   
  37.       v_count number := 0;   
  38.     begin   
  39.        
  40.         for utp in cur_utp(v_table_name)  loop   
  41.   
  42.             /* dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name   
  43.             ||' high_value:'||utp.high_value  ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);   
  44.             */   
  45.   
  46.             v_high_value  := substr(utp.high_value , v_pos, (utp.high_value_length  - v_pos) );   
  47.             v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');   
  48.             --  dbms_output.put_line(v_partition_max_date);   
  49.             if ((v_begin_date is null or v_begin_date <= v_partition_max_date)   
  50.                               and (v_end_date is null or v_partition_max_date < v_end_date)) then   
  51.   
  52.                 --  dbms_output.put_line('v_begin_date > v_partition_max_date = true:');   
  53.                 --  dbms_output.put_line('v_begin_date:'||to_char(v_begin_date,'syyyy-mm-dd hh24:mi:ss.ff')||' v_partition_max_date:'||to_char(v_partition_max_date,'syyyy-mm-dd hh24:mi:ss.ff'));   
  54.   
  55.                  v_sqlexec := 'ALTER TABLE ' || utp.table_name || ' DROP PARTITION ' ||   
  56.                              utp.partition_name;   
  57.                 -- dbms_output.put_line('删除' || utp.table_name || '表分区=' || v_SqlExec);   
  58.                 DBMS_Utility.Exec_DDL_Statement(v_SqlExec);   
  59.                 v_count := v_count + 1;   
  60.             end if;   
  61.             ------判断是否不需要再删除 ,跳出循环   
  62.             if(v_partition_max_date > v_end_date) then   
  63.                -- dbms_output.put_line('exit start');   
  64.                 exit;   
  65.             end if;   
  66.         end loop;   
  67.         dbms_output.put_line('drop partition count:'||v_count);   
  68.     end;   
  69.        
  70. --------------------------------------------------------------------------------------------------------   
  71. --------------------------------------------------------------------------------------------------------   
  72. /**   
  73.         根据分区信息,以及指定的时间,添加分区   
  74.     */   
  75.     procedure   add_partition(v_table_name in varchar2 ,   
  76.                               v_tablespace_name_in in varchar2 default null,   
  77.                                v_begin_date timestamp default null,    
  78.                                v_end_date timestamp)   
  79.     is   
  80.     -- Local variables here   
  81.         i integer;   
  82.      
  83.         ---分区表信息   
  84.         cursor cur_utp(   
  85.           v_table_name in user_tab_partitions.table_name%TYPE   
  86.         )   
  87.         is     
  88.         select * from (select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp   
  89.         where utp.table_name = UPPER(v_table_name)    
  90.         order by utp.partition_position desc) utp   
  91.         where rownum = 1;   
  92.            
  93.            
  94.                  
  95.         v_high_value varchar2(255);--less than value信息   
  96.         v_partition_max_date timestamp;----less than value信息的 timestamp表示形式   
  97.         v_sqlexec VARCHAR2(2000); --DDL语句变量   
  98.         v_count number := 0;   
  99.         v_interver number := 1;--步长间隔 单位(天)   
  100.         v_part_name_header varchar2(20) :='part_';   
  101.         v_part_name varchar2(2000);--分区名   
  102.         v_partition_num number := 0;   
  103.         v_tablespace_name varchar2(200) ;   
  104.         v_end_date_inner timestamp;   
  105.     begin   
  106.                          
  107.                          
  108.         v_interver := 1/24;   
  109.         v_part_name_header := upper(v_part_name_header);   
  110.                         
  111.         v_end_date_inner := trunc(v_end_date);   
  112.         --取值   
  113.         for utp in cur_utp(v_table_name)  loop   
  114.                          
  115.             dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name    
  116.                                      ||' high_value:'||utp.high_value  ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);   
  117.                              
  118.                                              
  119.             v_high_value  := substr(utp.high_value , v_pos, (utp.high_value_length  - v_pos) );    
  120.             v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');   
  121.                
  122.             if(v_begin_date > v_partition_max_date) then   
  123.                  v_partition_max_date := v_begin_date;   
  124.             end if;   
  125.                              
  126.             v_part_name := utp.partition_name ;   
  127.                              
  128.             --如果没有给默认值   
  129.             if(v_tablespace_name_in is null) then   
  130.            -- v_tablespace_name    
  131.                  v_tablespace_name := utp.tablespace_name;   
  132.             else   
  133.                  v_tablespace_name := v_tablespace_name_in;   
  134.             end if;   
  135.                
  136.             dbms_output.put_line(v_partition_max_date);   
  137.                               
  138.         end loop;   
  139.                          
  140.         if( v_part_name_header = substr(v_part_name,1,length(v_part_name_header))) then    
  141.                        
  142.             dbms_output.put_line('expect part_name header:'||v_part_name);   
  143.             v_partition_num := to_number(substr(v_part_name,length(v_part_name_header)+1,length(v_part_name)));   
  144.             v_partition_num := v_partition_num + 1;   
  145.         else   
  146.             dbms_output.put_line('not expect part_name header user default:'||v_part_name);   
  147.             v_partition_num := 0;   
  148.         end if;   
  149.            
  150.                          
  151.         i := 0;   
  152.         v_partition_max_date := v_partition_max_date + v_interver;    
  153.                          
  154.         dbms_output.put_line('v_partition_max_date:'||to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')||' v_end_date_inner:' ||to_char(v_end_date_inner, 'syyyy-mm-dd hh24:mi:ss.ff') );   
  155.                          
  156.         while v_partition_max_date < v_end_date_inner    
  157.         loop   
  158.                              
  159.   
  160.              v_SqlExec := 'ALTER TABLE ' || v_table_name || ' ADD PARTITION ' ||   
  161.                      v_part_name_header || (v_partition_num + i)   
  162.                      || ' values less than(TIMESTAMP'''||   
  163.                      to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')    
  164.                      || ''') TABLESPACE ' || v_tablespace_name;   
  165.             dbms_output.put_line('创建 表分区' || i || '=' || v_SqlExec);   
  166.             --alter table IP_CONNRATE_LIMITEX_LOG add partition part_0002 values less than(TIMESTAMP'2010-08-31 00:00:00.000000') tablespace NASP_IP_LOG_TABLESPACE;   
  167.             DBMS_Utility.Exec_DDL_Statement(v_SqlExec);   
  168.                              
  169.             v_partition_max_date := v_partition_max_date + v_interver;                 
  170.             i := i +1;   
  171.                              
  172.         end loop;   
  173.         v_count := v_count + i;   
  174.         dbms_output.put_line('add partition count:'||v_count);   
  175.     end;    
  176.                                   
  177. --------------------------------------------------------------------------------------------------------   
  178. --------------------------------------------------------------------------------------------------------       
  179.     /**   
  180.         根据原来的最后分区信息,自动追加分区   
  181.     */   
  182.     procedure   append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,   
  183.                                v_end_date in timestamp)   
  184.     is   
  185.           
  186.     begin   
  187.         add_partition(v_table_name, v_tablespace_name_in, null,v_end_date);   
  188.     end;                   
  189.   
  190. end;  
create or replace package body pkg_partition_alter is           v_pos number := 11;--截断high_value中信息的字符部分起始位            /**      删除分区表中,指定时间段内的分区      ####      参数:      v_table_name 分区表名      v_begin_date 删除分区的开始时间 (null 不限定开始时间) 包含      v_end_date 删除分区的结束时间 (null 不限定结束时间)   不包含      ####      使用注意:      1.假定分区表中分区是按时间戳 timestamp 字段 来分区的      2.删除的指定时间段的分区,其是根据分区表创建时的less than value时间指定的      3.删除时如果指定 v_begin_date,v_end_date 则删除时是[v_begin_date,v_end_date)区间      4.只有最后一个分区时是无法删除的      **/      procedure drop_partition(      v_table_name in varchar2 ,      v_begin_date timestamp default null,      v_end_date timestamp default null      )      is        ---分区表信息        cursor cur_utp(          v_table_name in user_tab_partitions.table_name%TYPE        )        is        select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp        where utp.table_name = UPPER(v_table_name)        order by utp.partition_position ;        v_high_value varchar2(255);--less than value信息        v_partition_max_date timestamp;----less than value信息的 timestamp表示形式        v_sqlexec VARCHAR2(2000); --DDL语句变量        v_count number := 0;      begin                for utp in cur_utp(v_table_name)  loop                /* dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name              ||' high_value:'||utp.high_value  ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);              */                v_high_value  := substr(utp.high_value , v_pos, (utp.high_value_length  - v_pos) );              v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');              --  dbms_output.put_line(v_partition_max_date);              if ((v_begin_date is null or v_begin_date <= v_partition_max_date)                                and (v_end_date is null or v_partition_max_date < v_end_date)) then                    --  dbms_output.put_line('v_begin_date > v_partition_max_date = true:');                  --  dbms_output.put_line('v_begin_date:'||to_char(v_begin_date,'syyyy-mm-dd hh24:mi:ss.ff')||' v_partition_max_date:'||to_char(v_partition_max_date,'syyyy-mm-dd hh24:mi:ss.ff'));                     v_sqlexec := 'ALTER TABLE ' || utp.table_name || ' DROP PARTITION ' ||                               utp.partition_name;                  -- dbms_output.put_line('删除' || utp.table_name || '表分区=' || v_SqlExec);                  DBMS_Utility.Exec_DDL_Statement(v_SqlExec);                  v_count := v_count + 1;              end if;              ------判断是否不需要再删除 ,跳出循环              if(v_partition_max_date > v_end_date) then                 -- dbms_output.put_line('exit start');                  exit;              end if;          end loop;          dbms_output.put_line('drop partition count:'||v_count);      end;        --------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------  /**          根据分区信息,以及指定的时间,添加分区      */      procedure   add_partition(v_table_name in varchar2 ,                                v_tablespace_name_in in varchar2 default null,                                 v_begin_date timestamp default null,                                  v_end_date timestamp)      is      -- Local variables here          i integer;              ---分区表信息          cursor cur_utp(            v_table_name in user_tab_partitions.table_name%TYPE          )          is            select * from (select utp.table_name,utp.tablespace_name,utp.partition_name,utp.high_value,utp.high_value_length,utp.partition_position from user_tab_partitions utp          where utp.table_name = UPPER(v_table_name)           order by utp.partition_position desc) utp          where rownum = 1;                                              v_high_value varchar2(255);--less than value信息          v_partition_max_date timestamp;----less than value信息的 timestamp表示形式          v_sqlexec VARCHAR2(2000); --DDL语句变量          v_count number := 0;          v_interver number := 1;--步长间隔 单位(天)          v_part_name_header varchar2(20) :='part_';          v_part_name varchar2(2000);--分区名          v_partition_num number := 0;          v_tablespace_name varchar2(200) ;          v_end_date_inner timestamp;      begin                                                          v_interver := 1/24;          v_part_name_header := upper(v_part_name_header);                                 v_end_date_inner := trunc(v_end_date);          --取值          for utp in cur_utp(v_table_name)  loop                                      dbms_output.put_line('table_name:'|| utp.table_name ||' tablespace_name:'|| utp.tablespace_name||' partition_name:'||utp.partition_name                                        ||' high_value:'||utp.high_value  ||' high_value_length:'||utp.high_value_length||' partition_position:'||utp.partition_position);                                                                                      v_high_value  := substr(utp.high_value , v_pos, (utp.high_value_length  - v_pos) );               v_partition_max_date := to_timestamp(v_high_value , 'syyyy-mm-dd hh24:mi:ss.ff');                            if(v_begin_date > v_partition_max_date) then                   v_partition_max_date := v_begin_date;              end if;                                          v_part_name := utp.partition_name ;                                          --如果没有给默认值              if(v_tablespace_name_in is null) then             -- v_tablespace_name                    v_tablespace_name := utp.tablespace_name;              else                   v_tablespace_name := v_tablespace_name_in;              end if;                            dbms_output.put_line(v_partition_max_date);                                       end loop;                                  if( v_part_name_header = substr(v_part_name,1,length(v_part_name_header))) then                                     dbms_output.put_line('expect part_name header:'||v_part_name);              v_partition_num := to_number(substr(v_part_name,length(v_part_name_header)+1,length(v_part_name)));              v_partition_num := v_partition_num + 1;          else              dbms_output.put_line('not expect part_name header user default:'||v_part_name);              v_partition_num := 0;          end if;                                            i := 0;          v_partition_max_date := v_partition_max_date + v_interver;                                   dbms_output.put_line('v_partition_max_date:'||to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')||' v_end_date_inner:' ||to_char(v_end_date_inner, 'syyyy-mm-dd hh24:mi:ss.ff') );                                  while v_partition_max_date < v_end_date_inner           loop                                             v_SqlExec := 'ALTER TABLE ' || v_table_name || ' ADD PARTITION ' ||                       v_part_name_header || (v_partition_num + i)                       || ' values less than(TIMESTAMP'''||                       to_char(v_partition_max_date, 'syyyy-mm-dd hh24:mi:ss.ff')                        || ''') TABLESPACE ' || v_tablespace_name;              dbms_output.put_line('创建 表分区' || i || '=' || v_SqlExec);              --alter table IP_CONNRATE_LIMITEX_LOG add partition part_0002 values less than(TIMESTAMP'2010-08-31 00:00:00.000000') tablespace NASP_IP_LOG_TABLESPACE;              DBMS_Utility.Exec_DDL_Statement(v_SqlExec);                                          v_partition_max_date := v_partition_max_date + v_interver;                            i := i +1;                                      end loop;          v_count := v_count + i;          dbms_output.put_line('add partition count:'||v_count);      end;                                    --------------------------------------------------------------------------------------------------------  --------------------------------------------------------------------------------------------------------          /**          根据原来的最后分区信息,自动追加分区      */      procedure   append_partition(v_table_name in varchar2 ,v_tablespace_name_in in varchar2 default null,                                 v_end_date in timestamp)      is               begin          add_partition(v_table_name, v_tablespace_name_in, null,v_end_date);      end;                    end;  

 

下面是调用的示例

在job中调用 设定 15天调用一次,为安全,创建和删除 30天的分区

pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);

--可以指定表空间
pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);

 

job( oracle)

Pl/sql代码
  1. begin   
  2.   sys.dbms_job.submit(job => :job,   
  3.                       what => pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);',   
  4.                       next_date => to_date('05-10-2010''dd-mm-yyyy'),   
  5.                       interval => 'TRUNC(SYSDATE + 15)');   
  6.   commit;   
  7. end;   
  8. /  
begin    sys.dbms_job.submit(job => :job,                        what => pkg_partition_alter.drop_partition ('test_partition_log',null,sysdate - 30);pkg_partition_alter.append_partition('test_partition_log','TEST_TABLESPACE',sysdate + 30);',                        next_date => to_date('05-10-2010', 'dd-mm-yyyy'),                        interval => 'TRUNC(SYSDATE + 15)');    commit;  end;  /

 

 

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

 

 http://1985wanggang.blog.163.com/blog/static/7763833201081455532590/

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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