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, 并且还需要有操作其他用户下表的权限. 这样会导致权限放大, 建议不要这么操作.
如果各个用户都需要使用动态分区扩展与清理, 可以在每个用户下边部署这个存储过程, 这样就不用跨用户之间动态管理分区.
来源: http://www.linuxidc.com/Linux/2018-11/155596.htm