1.现有四张表从全局索引改造成local索引http://www.cndba.cn/hbhe0316/article/116470

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;

2.修改需要改造的其他三张表

RENAME HISTORY_TEXT TO HISTORY_TEXT_20230303;
RENAME HISTORY_LOG TO HISTORY_LOG_20230303;
RENAME HISTORY_STR TO HISTORY_STR_20230303;

3.分区表建表SQLhttp://www.cndba.cn/hbhe0316/article/116470

-- 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;

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle