阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle 动态添加分区的实现方法

242次阅读
没有评论

共计 3008 个字符,预计需要花费 8 分钟才能阅读完成。

Oracle 表分区目的:

在数据处理过程中,通常对于数据比较大的表进行分区管理,而分区的依据往往是数据日期,每一天或者每几天数据存储在一个指定的分区中,当数据量一天天增加后,通过分区进行过滤,有利于快速查询某一天的数据。

在向分区表中插入数据时,分区表必须有能够装载这条数据的分区,比如将 2018-01-08 的数据全部放在 P20180102 这个分区,而这个分区条件是数据日期小于等于 2018-01-02,那么这条数据日期为 2018-01-08 的数据就无法 insert 到这张表,这样就会出现错误。

为了解决为分区表自动扩展分区的需求,我们编写了一个存储过程,用来在向表中 insert 数据时,动态的对表进行添加分区或清除分区。只需要在 insert 之前,执行下边存储过程即可。

示例代码如下:

create or replace procedure manage_table_partitions(
    tname varchar2,
    curDate date
) is
    IS_PART_EXISTS        integer          := 0;
    IS_TABLE_EXISTS        integer          := 0;
    IS_PART_TABLE          integer          := 0;
    P_LABEL                varchar2(30)    := to_char(curDate,’YYYYMMDD’);
    MAX_PARTITION_DATE    date;
    MIN_PARTITION_DATE    date;
    TARGET_TABLE          varchar2(40)    := upper(trim(tname));
    V_SQL                  varchar2(3000)  := ”;

— 定义异常类型变量
    no_table_exception          exception;
    less_than_latest_exception  exception;

— 固定参数
  ADD_FREQ                integer          := 1;
begin

    — 查看这张表是否为分区表
    select count(*) into IS_PART_TABLE from user_part_tables
    where table_name = TARGET_TABLE;

    if IS_PART_TABLE <> 1 then
        select count(*) into IS_TABLE_EXISTS from tab where tname = TARGET_TABLE;
        if IS_TABLE_EXISTS <> 1 then
            dbms_output.put_line(tname||’,这张表不存在 ’);
            raise no_table_exception;
        end if;

        dbms_output.put_line(tname||’,这张表不是分区表,将直接清空表中数据 ’);
        V_SQL := ‘truncate table ‘ || tname;
        execute immediate V_SQL;
        return ;
    end if;

    — 查看分区是否存在
    select count(*) into IS_PART_EXISTS
    from user_tab_partitions
    where table_name = TARGET_TABLE
        and partition_name = ‘P’||P_LABEL
    ;

    if IS_PART_EXISTS <> 1 then
   
        — 查看分区表最大分区和最小分区
        select 
            max(to_date(substr(partition_name,2),’YYYY-MM-DD’))
            ,min(to_date(substr(partition_name,2),’YYYY-MM-DD’))
        into
            MAX_PARTITION_DATE
            ,MIN_PARTITION_DATE
        from user_tab_partitions
        where table_name = TARGET_TABLE
        group by table_name;

        — 检查准备创建的分区是否小于当前表中分区最小日期
        if MIN_PARTITION_DATE > curDate then
            dbms_output.put_Line(‘ 数据日期已经小于分区表最小日期,请重建表,重新设定最小日期分区 ’);
            raise less_than_latest_exception;
        end if;

        dbms_output.put_line(‘ 添加分区,按照指定频率添加分区 ’);
        MAX_PARTITION_DATE := MAX_PARTITION_DATE + ADD_FREQ;
        while MAX_PARTITION_DATE <= curDate loop
            begin
                V_SQL := ‘alter table ‘|| tname || ‘ add partition P’ || to_char(MAX_PARTITION_DATE,’YYYYMMDD’) || ‘ values less than ‘;
                V_SQL := V_SQL || ‘(to_date(”’ || to_char(MAX_PARTITION_DATE + ADD_FREQ,’YYYY-MM-DD’) ||”’,”YYYY-MM-DD”))’;
                –dbms_output.put_line(V_SQL);
                execute immediate V_SQL;
                MAX_PARTITION_DATE := MAX_PARTITION_DATE + ADD_FREQ;
            end;
        end loop;
    else
        dbms_output.put_line(‘ 清除分区中的数据 ’);
        V_SQL := ‘alter table ‘||tname||’ truncate partition P’||P_LABEL;
        dbms_output.put_line(V_SQL);
        execute immediate V_SQL;
    end if;       
end manage_table_partitions;

上边这段程序,默认情况下查询的是用户自己的表,如 user_tab_partitions,user_part_tables,tab。所以,默认只能对用户自己的表的分区进行动态扩展和分区数据清除。如果想要对其他用户的表进行动态分区管理,需要将 user_tab_partitions,user_part_tables,tab 换成 dba_tab_partitions,dba_part_tables,dba_tables,并且还需要有操作其他用户下表的权限。这样会导致权限放大,建议不要这么操作。

如果各个用户都需要使用动态分区扩展与清理,可以在每个用户下边部署这个存储过程,这样就不用跨用户之间动态管理分区。

更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3008字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中