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

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

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

 

先创建包头:

 

 

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;

 再创建包的实现部分

 

 

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)

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;
/

 

 参见我的163博客:http://1985wanggang.blog.163.com/blog/static/7763833201081455532590/