创建分区表
插入数据时,表会依据数据内容自动添加分区
小时分区表
create table F_TEMP_H
(
sdate DATE,
city VARCHAR2(20),
area VARCHAR2(20),
name VARCHAR2(20),
age NUMBER(20),
score NUMBER(20)
)
TABLESPACE biaokongjianming
PARTITION BY RANGE (sdate)
INTERVAL(NUMTODSINTERVAL(1,'Hour'))
(
partition PART_INIT values less than (TIMESTAMP' 2022-01-01 00:00:00')
NOLOGGING
NOCOMPRESS
TABLESPACE biaokongjianming
)
NOCACHE
NOPARALLEL
MONITORING;
天分区表
create table F_TEMP_D
(
sdate DATE,
city VARCHAR2(20),
area VARCHAR2(20),
name VARCHAR2(20),
age NUMBER(20),
score NUMBER(20)
)
TABLESPACE biaokongjianming
PARTITION BY RANGE (sdate)
INTERVAL(NUMTODSINTERVAL(1,'Day'))
(
partition PART_INIT values less than (TIMESTAMP' 2022-01-01 00:00:00')
NOLOGGING
NOCOMPRESS
TABLESPACE biaokongjianming
)
NOCACHE
NOPARALLEL
MONITORING;
其他指令
1.添加分区语句
alter table f_temp_h add partition p2022061820 values ('2022-06-18 20:00:00');
2.删除分区语句
alter table f_temp_h drop partition p2022061820;
alter table F_VOLTE_VOICE_CI_H drop partition SYS_P37815;
alter table F_VOLTE_VOICE_CI_H drop partition SYS_P37835;
3.删除某分区的全部数据
alter table f_temp_h truncate partition p2022061820;