Oracle动态添加分区

zhou66

转贴：Oracle动态添加分区

存储过程：

1. create or replace procedure manage_table_partitions(
   
2.      tname varchar2,
   
3.      curDate date
   
4. ) is
   
5.     IS_PART_EXISTS         integer          := 0;
   
6.     IS_TABLE_EXISTS        integer          := 0;
   
7.     IS_PART_TABLE          integer          := 0;
   
8.     P_LABEL                varchar2(30)     := to_char(curDate,'YYYYMMDD');
   
9.     MAX_PARTITION_DATE     date;
   
10.     MIN_PARTITION_DATE     date;
    
11.     TARGET_TABLE           varchar2(40)     := upper(trim(tname));
    
12.     V_SQL                  varchar2(3000)   := '';
    
13. 
    
14. -- 定义异常类型变量
    
15.     no_table_exception          exception;
    
16.     less_than_latest_exception  exception;
    
17. 
    
18. -- 固定参数
    
19.    ADD_FREQ                integer          := 1;
    
20. begin
    
21. 
    
22.     -- 查看这张表是否为分区表
    
23.     select count(*) into IS_PART_TABLE from user_part_tables
    
24.     where table_name = TARGET_TABLE;
    
25. 
    
26.     if IS_PART_TABLE <> 1 then
    
27.         select count(*) into IS_TABLE_EXISTS from tab where tname = TARGET_TABLE;
    
28.         if IS_TABLE_EXISTS <> 1 then
    
29.             dbms_output.put_line(tname||'，这张表不存在');
    
30.             raise no_table_exception;
    
31.         end if;
    
32. 
    
33.         dbms_output.put_line(tname||'，这张表不是分区表，将直接清空表中数据');
    
34.         V_SQL := 'truncate table ' || tname;
    
35.         execute immediate V_SQL;
    
36.         return ;
    
37.     end if;
    
38. 
    
39. 
    
40.     -- 查看分区是否存在
    
41.     select count(*) into IS_PART_EXISTS
    
42.     from user_tab_partitions
    
43.     where table_name = TARGET_TABLE
    
44.          and partition_name = 'P'||P_LABEL
    
45.     ;
    
46. 
    
47.     if IS_PART_EXISTS <> 1 then
    
48.    
    
49.         -- 查看分区表最大分区和最小分区
    
50.         select  
    
51.              max(to_date(substr(partition_name,2),'YYYY-MM-DD'))
    
52.              ,min(to_date(substr(partition_name,2),'YYYY-MM-DD'))
    
53.         into
    
54.              MAX_PARTITION_DATE
    
55.              ,MIN_PARTITION_DATE
    
56.         from user_tab_partitions
    
57.         where table_name = TARGET_TABLE
    
58.         group by table_name;
    
59. 
    
60.         -- 检查准备创建的分区是否小于当前表中分区最小日期
    
61.         if MIN_PARTITION_DATE > curDate then
    
62.             dbms_output.put_Line('数据日期已经小于分区表最小日期，请重建表，重新设定最小日期分区');
    
63.             raise less_than_latest_exception;
    
64.         end if;
    
65. 
    
66.         dbms_output.put_line('添加分区，按照指定频率添加分区');
    
67.         MAX_PARTITION_DATE := MAX_PARTITION_DATE + ADD_FREQ;
    
68.         while MAX_PARTITION_DATE <= curDate loop
    
69.             begin
    
70.                 V_SQL := 'alter table '|| tname || ' add partition P' || to_char(MAX_PARTITION_DATE,'YYYYMMDD') || ' values less than ';
    
71.                 V_SQL := V_SQL || '(to_date(''' || to_char(MAX_PARTITION_DATE + ADD_FREQ,'YYYY-MM-DD') ||''',''YYYY-MM-DD''))';
    
72.                 --dbms_output.put_line(V_SQL);
    
73.                 execute immediate V_SQL;
    
74.                 MAX_PARTITION_DATE := MAX_PARTITION_DATE + ADD_FREQ;
    
75.             end;
    
76.         end loop;
    
77.     else
    
78.         dbms_output.put_line('清除分区中的数据');
    
79.         V_SQL := 'alter table '||tname||' truncate partition P'||P_LABEL;
    
80.         dbms_output.put_line(V_SQL);
    
81.         execute immediate V_SQL;
    
82.     end if;        
    
83. end manage_table_partitions;
    

复制代码

zhou66

在数据处理过程中，通常对于数据比较大的表进行分区管理，而分区的依据往往是数据日期，每一天或者每几天数据存储在一个指定的分区中，当数据量一天天增加后，通过分区进行过滤，有利于快速查询某一天的数据。
在向分区表中插入数据时，分区表必须有能够装载这条数据的分区，比如将2018-01-08的数据全部放在P20180102这个分区，而这个分区条件是数据日期小于等于2018-01-02，那么这条数据日期为2018-01-08的数据就无法insert到这张表，这样就会出现错误。
为了解决为分区表自动扩展分区的需求，我们编写了一个存储过程，用来在向表中insert数据时，动态的对表进行添加分区或清除分区。只需要在insert之前，执行下边存储过程即可。

存储过程如楼上。

zhou66

（1）
numtoyminterval (<x>,<c>)

将 x 转为 interval year to month 数据类型。

常用的单位有 ('year','month')。

测试一下：

select sysdate, sysdate + numtoyminterval(3, 'year') as res from dual;

（2）
numtodsinterval(<x>,<c>) ，x 是一个数字，c 是一个字符串。

把 x 转为 interval day to second 数据类型。

常用的单位有 ('day','hour','minute','second')。

测试一下：

select sysdate, sysdate + numtodsinterval(4,'hour') as res from dual;
————————————————
版权声明：本文为CSDN博主「力哥讲技术」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/m0_37723088/article/details/129472436

Marco-1988

感谢分享!!

abjun

感谢分享!!