1. 手动分区实践
手动分区的实践，本文的仅拿Zabbix的TRENDS表作为讲解，要对其他表做分区，是一样的套路。 还有，在做这些操作前，请停止上层应用（Zabbix），如果是线上环境，请在合规的时间进行操作。
1.1 对现有表重命名，做好备份
语法
- 语法1：ALTER TABLE table_name RENAME TO new_table_name;
- 语法2：RENAME table_name TO new_table_name;
ALTER TABLE TRENDS RENAME TO TRENDS_OLD_TEMP;
1.2 重新建表（range分区）
范围分区，以列的值（此处以CLOCK）的范围来做为分区的划分条件
-- 创建表 create table TRENDS ( ITEMID NUMBER(20) not null, CLOCK NUMBER(10) default '0' not null, NUM NUMBER(10) default '0' not null, VALUE_MIN NUMBER(20,4) default '0.0000' not null, VALUE_AVG NUMBER(20,4) default '0.0000' not null, VALUE_MAX NUMBER(20,4) default '0.0000' not null )partition by range (CLOCK)( partition P_MAX values less than (MAXVALUE) ); -- 创建/重新创建主键约束、唯一约束和外键约束 alter table TRENDS add primary key (ITEMID, CLOCK) using index
range分区是应用最广的分区方式，它是以列的值（此处以CLOCK）的范围来做为分区的划分条件，将记录存放到列值所在的 range 分区中，因此在创建的时候，需要指定基于的列，以及分区的范围值，如果某些记录暂无法预测范围，可以创建 maxvalue 分区（此处就是这个方式，分区名叫P_MAX），所有不在指定范围内的记录都会被存储到 maxvalue 所在分区中，并且支持多列做为依赖列。每个分区内储存的数据小于该分区指定的values less than数值，除第一个分区外，其它分区都有最小值且等于上一分区指定的values less than数值。
1.3 （可选）如果原来的数据还要的话，可以将 TRENDS_OLD_TEMP 表里的数据迁移到 TRENDS 表中
insert into TRENDS select * from TRENDS_OLD_TEMP; COMMIT;
1.4 （可选）如果TRENDS_OLD_TEMP表不要了，就可以删掉
drop table trends_old_temp;
1.4 将RANGE分区划分为两个分区
- 参考：
-- 前面那个sql生成id，然后替换进去 select ZBX_ORACLE.ORACLE_TO_UNIX(to_date('2022-12-07','yyyy-mm-dd')) from dual; -- 时间和分区保持一致 alter table ZBX_ORACLE.HISTORY_UINT split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.HISTORY split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.HISTORY_TEXT split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.TRENDS_UINT split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.TRENDS split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.HISTORY_LOG split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.HISTORY_STR split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.ALERTS split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.EVENTS split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes; alter table ZBX_ORACLE.AUDITLOG split partition P_MAX at(1670342400) into (partition P_20221207,partition P_MAX) update global indexes;
- 实战：
-- 1）创建好ORACLE_TO_UNIX函数 CREATE OR REPLACE FUNCTION ORACLE_TO_UNIX(in_date IN DATE) return number is begin return( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600); end oracle_to_unix; -- 2）执行下面的sql生成id，本次为1670428800 select ORACLE_TO_UNIX(to_date('2022-12-08','yyyy-mm-dd')) from dual; -- 3）将上一步得到的id：1670428800传入at()函数，时间和分区名保持一致，这里是P_20221208 alter table TRENDS split partition P_MAX at(1670428800) into (partition P_20221208,partition P_MAX) update global indexes;
1.5 查询分区表
select segment_name,segment_type,partition_name, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'ZBX5_USER' and partition_name like 'P_%' group by segment_name,segment_type,partition_name;
2. 存储过程+定时任务 实现自动表分区的操作
说明：在本次使用的存储过程代码中，有使用到相关函数和额外的表，需提前创建好。
2.1 创建有调用到的函数
CREATE OR REPLACE FUNCTION date_to_unix_ts( p_date IN DATE ) RETURN number IS l_number number(10); BEGIN l_number := to_number((p_date - to_date('1970-01-01','YYYY-MM-DD')) * (24*60*60)); RETURN l_number; END;
2.2 创建有使用到的表
-- Create table create table T_PART_MAINTAIN_ERR ( ER_DATE DATE, ER_TAB VARCHAR2(30), ER_PARTNAME VARCHAR2(30), ER_OPT VARCHAR2(20), ER_CODE VARCHAR2(20), ER_MSG VARCHAR2(60) ); -- Create/Recreate indexes create index IX_T_PART_MAIN_ERR_DATE on T_PART_MAINTAIN_ERR (ER_DATE);
2.3 创建存储过程
请注意创建顺序。
- 1）按天创建分区的存储过程：p_create_partition
create or replace procedure p_create_partition(p_tab in varchar2,p_partname in varchar2,p_clock in number) as v_tabname varchar2(30); v_partname varchar2(30); v_clock number; v_sql varchar2(1000); v_count integer; v_sqlcode varchar2(20); v_sqlmsg varchar2(60); begin v_tabname := upper(p_tab); v_partname := upper(p_partname); v_clock := p_clock; v_count :=0; select count(*) into v_count from user_tab_partitions where table_name = v_tabname and partition_name = v_partname; if v_count = 0 then v_sql := 'alter table '||v_tabname||' split partition p_max at('||v_clock||') into ( partition '||v_partname||',partition p_max) update global indexes'; execute immediate v_sql; end if; EXCEPTION WHEN OTHERS THEN v_sqlcode := substr(SQLCODE,1,20); v_sqlmsg := substr(SQLERRM,1,60); insert into t_part_maintain_err values(sysdate,v_tabname,v_partname,'create partition',v_sqlcode,v_sqlmsg); commit; end;
- 2）生成时间和检查分区是否存在等功能的存储过程：p_create_next_partitions
create or replace procedure p_create_next_partitions(p_tabname in varchar2) as v_date date; v_tabname varchar2(30); v_partname varchar2(30); v_clock number; v_clock_date date; v_total_days integer; v_day integer; v_sqlcode varchar2(20); v_sqlmsg varchar2(60); begin v_total_days := 7; v_date := sysdate; v_tabname := p_tabname; for v_day in 1..v_total_days loop v_date := trunc(sysdate + v_day); v_partname := 'p_'||to_char(v_date,'YYYYMMDD'); v_clock_date := trunc(v_date + 1); v_clock := date_to_unix_ts(v_clock_date); p_create_partition(v_tabname,v_partname,v_clock); end loop; EXCEPTION WHEN OTHERS THEN v_sqlcode := substr(SQLCODE,1,20); v_sqlmsg := substr(SQLERRM,1,60); insert into t_part_maintain_err values(sysdate,v_tabname,v_partname,'call create partition',v_sqlcode,v_sqlmsg); commit; end;
- 3）总调度存储过程：p_maintain_partitions
create or replace procedure p_maintain_partitions as v_sqlcode varchar2(20); v_sqlmsg varchar2(60); begin p_create_next_partitions('TRENDS'); EXCEPTION WHEN OTHERS THEN v_sqlcode := substr(SQLCODE,1,20); v_sqlmsg := substr(SQLERRM,1,60); insert into t_part_maintain_err values(sysdate,' ',' ','p_maintain_partitions',v_sqlcode,v_sqlmsg); commit; end;
说明，以后通过存储过程来对其他表进行创建分区，只需要在总调度的存储过程（p_maintain_partitions）中，增加 p_create_next_partitions('XXXX');，比如要增加HISTORY表，p_create_next_partitions('HISTORY')。但在这之前，请确保HISTORY这个表已经是range分区的表，可参考本文中的1.2小节。
2.4 创建定时任务
疑问：存储过程创建好，要编译吗？还是说，存储过程创建好，就可以创job了？答案：会自动编译的
下面对创建jon的sql语句进行简单说明，如下：
variable job number; begin sys.dbms_job.submit(job => :job, what => 'p_maintain_partitions;', next_date => to_date('06-12-2022 09:00:00', 'dd-mm-yyyy hh24:mi:ss'), interval => 'trunc(sysdate)+1+9/24'); commit; end; /
to_date的说明：定时任务是每台早上9点钟执行。to_date函数中的年月日可以改，比如改成当前的日期，如果当前日期的时间已经过了早上9点，则会马上执行这个定时任务，如果日期改成当前日期的明天，那么将会在明天的9点执行该定时任务。 疑问：最下面个 / 不要了吧？答案：要的，在命令行执行，那个是提交。
下面开始在命令行上执行：
根据数据库组专家对to_date的说明，我创建job后并没有马上执行，于是手动运行了一次，
鼠标右击这个job，选择“运行”
运行后，再次查询表分区
已经创建了未来7天的分区，当天的那个（P_20221208）是我手动创建的，经验证，存储过程不会创建当天的分区。
3. 分区表常用管理
查询表的分区
select segment_name,segment_type,partition_name, sum(bytes)/1024/1024/1024 GB from dba_segments where owner = 'ZBX5_USER' and partition_name like 'P_%' group by segment_name,segment_type,partition_name;
手动删除分区
alter table TRENDS drop partition P_20221208 update gl
