今日完成了这个任务,特做总结
首先,建立分区表。
CREATE TABLE MALS_NM_CPU_INFO_T_NEW
(
HOST_IP VARCHAR2(40 BYTE) NOT NULL,
HOST_NAME VARCHAR2(15 BYTE) NOT NULL,
COMMIT_TM VARCHAR2(14 BYTE) NOT NULL,
SYS_ID VARCHAR2(32 BYTE) NOT NULL,
CPU_FREQUENCY NUMBER(10),
CPU_LOAD NUMBER(3)
)
PARTITION BY RANGE (COMMIT_TM) --以COMMIT_TM字段做分区条件
(PARTITION P_NM_CPU_INFO_20081120 VALUES LESS THAN ('20081121'));
若是已经建立了表,但是却没有分区,就只能根据已经有的表新建分区表了,如下:
CREATE TABLE MALS_NM_CPU_INFO_T_NEW
(
HOST_IP VARCHAR2(40 BYTE) NOT NULL,
HOST_NAME VARCHAR2(15 BYTE) NOT NULL,
COMMIT_TM VARCHAR2(14 BYTE) NOT NULL,
SYS_ID VARCHAR2(32 BYTE) NOT NULL,
CPU_FREQUENCY NUMBER(10),
CPU_LOAD NUMBER(3)
)
PARTITION BY RANGE (COMMIT_TM)
(PARTITION P_NM_CPU_INFO_20081120 VALUES LESS THAN ('20081121'));
insert into MALS_NM_CPU_INFO_T_NEW select * from MALS_NM_CPU_INFO_T;
rename MALS_NM_CPU_INFO_T to MALS_NM_CPU_INFO_T_old;
rename MALS_NM_CPU_INFO_T_new to MALS_NM_CPU_INFO_T;
create index nm_cpu_info_index on MALS_NM_CPU_INFO_T(Commit_Tm); --建立局部分区索引,默认与当前表分区,在一个表空间中
此时建立完表,在TOAD中会在表旁边看见 分区 图标
其次,建立增加分区的存储过程。
CREATE OR REPLACE PROCEDURE MALS_NM_ADD_PARTITION_PROC(