Oracle创建分区表（按年、季、月、日自动分区）

这种分区方式会根据插入的数据自动增加分区，不需要手工维护分区字段必须是日期类型按年分区create table test.test_r1(id NUMBER(20) not null primary key,name VARCHAR2(20) not null,joindate DATE not null) tablespace testpartition by range(joindate) i

guang.yan

11154人浏览 · 2022-05-09 11:57:44

guang.yan · 2022-05-09 11:57:44 发布

这种分区方式会根据插入的数据自动增加分区，不需要手工维护
分区字段必须是日期类型

按年分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(1,'year'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

按季度分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(3,'month'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

按月分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtoyminterval(1,'month'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

按天分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtodsinterval(1,'day'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);

按周分区

create table test.test_r1
(
id NUMBER(20) not null primary key,
name VARCHAR2(20) not null,
joindate DATE not null
) tablespace test
partition by range(joindate) interval (numtodsinterval(7,'day'))
(partition p_before_2021 values less than (to_date('2022-01-01','YYYY-MM-DD'))
);