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

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，并且还需要有操作其他用户下表的权限。这样会导致权限放大，建议不要这么操作。

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