清理历史数据的操作最简单的可以直接在 zabbix 的配置界面中重新设定历史数据的保存和清理期限,另外也可以直接使用数据库指令清理
在 zabbix 中历史数据其实包括2个空间占用很大的表: history (历史表)和 trend (趋势表)
可以同时进行,
清理数据数据,将历史表修改为分区表,这样会使得系统稳定,快速。
alter table ZABBIX.HISTORY_UINT drop PRIMARY KEY;
alter table ZABBIX.HISTORY_UINT add CONSTRAINT PK_HISTORY_UINT PRIMARY KEY(ITEMID, CLOCK, NS, UPD_TIME) using index local tablespace ZABBIX_SCHE_HISTORY_TBS;
alter table ZABBIX.HISTORY drop PRIMARY KEY;
alter table ZABBIX.HISTORY add constraint PK_HISTORY PRIMARY KEY(ITEMID, CLOCK, NS, UPD_TIME) using index local tablespace ZABBIX_SCHE_HISTORY_TBS;
alter table ZABBIX.TRENDS_UINT drop PRIMARY KEY;
alter table ZABBIX.TRENDS_UINT add CONSTRAINT PK_TRENDS_UINT PRIMARY KEY(ITEMID, CLOCK, UPD_TIME) using index local tablespace ZABBIX_SCHE_HISTORY_TBS;
alter table ZABBIX.TRENDS drop PRIMARY KEY;
alter table ZABBIX.TRENDS add CONSTRAINT PK_TRENDS PRIMARY KEY(ITEMID, CLOCK, UPD_TIME) using index local tablespace ZABBIX_SCHE_HISTORY_TBS;
RENAME HISTORY_TEXT TO HISTORY_TEXT_20230303;
RENAME HISTORY_LOG TO HISTORY_LOG_20230303;
RENAME HISTORY_STR TO HISTORY_STR_20230303;
-- Create table
create table ZABBIX.HISTORY_TEXT
(
itemid NUMBER(20) not null,
clock NUMBER(10) default '0' not null,
value NCLOB default '',
ns NUMBER(10) default '0' not null,
upd_time TIMESTAMP(6) default SYSTIMESTAMP not null,
ins_time as ("ZABBIX"."UNIX_TO_ORACLE"("CLOCK"))
) tablespace ZABBIX_SCHE_DATA_TBS
partition by range(UPD_TIME) interval (numtoyminterval(1,'month'))
(partition P_20230301 values less than (TIMESTAMP' 2023-03-01 00:00:00')
);
alter table ZABBIX.HISTORY_TEXT add CONSTRAINT PK_HISTORY_TEXT primary key (ITEMID, CLOCK, NS, UPD_TIME) using index local tablespace ZABBIX_SCHE_DATA_TBS;
create table ZABBIX.HISTORY_LOG
(
itemid NUMBER(20) not null,
clock NUMBER(10) default '0' not null,
timestamp NUMBER(10) default '0' not null,
source NVARCHAR2(64) default '',
severity NUMBER(10) default '0' not null,
value NCLOB default '',
logeventid NUMBER(10) default '0' not null,
ns NUMBER(10) default '0' not null,
upd_time TIMESTAMP(6) default SYSTIMESTAMP not null,
ins_time as ("ZABBIX"."UNIX_TO_ORACLE"("CLOCK"))
) tablespace ZABBIX_SCHE_DATA_TBS
partition by range(UPD_TIME) interval (numtoyminterval(1,'month'))
(partition P_20230301 values less than (TIMESTAMP' 2023-03-01 00:00:00')
);
alter table ZABBIX.HISTORY_LOG add CONSTRAINT PK_HISTORY_LOG primary key (ITEMID, CLOCK, NS, UPD_TIME) using index local tablespace ZABBIX_SCHE_DATA_TBS;
create table ZABBIX.HISTORY_STR
(
itemid NUMBER(20) not null,
clock NUMBER(10) default '0' not null,
value NVARCHAR2(255) default '',
ns NUMBER(10) default '0' not null,
upd_time TIMESTAMP(6) default SYSTIMESTAMP not null,
ins_time as ("ZABBIX"."UNIX_TO_ORACLE"("CLOCK"))
) tablespace ZABBIX_SCHE_DATA_TBS
partition by range(UPD_TIME) interval (numtoyminterval(1,'month'))
(partition P_20230301 values less than (TIMESTAMP' 2023-03-01 00:00:00')
);
alter table ZABBIX.HISTORY_STR add CONSTRAINT PK_HISTORY_STR primary key (ITEMID, CLOCK, NS, UPD_TIME) using index local tablespace ZABBIX_SCHE_DATA_TBS;
收起