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

前言

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

---

一、按年分区 :

1、新建表、新建分区

create table pt_range_test2(
  pid         number(10),
  pname       varchar2(30),
  create_date date
) partition by range(create_date)(
  partition p_2019 values less than(to_date('2020-01-01', 'YYYY-MM-DD')),
  partition p_2020 values less than(to_date('2021-01-01', 'YYYY-MM-DD')),
  partition p_2021 values less than(to_date('2022-01-01', 'YYYY-MM-DD')),
  partition p_2022 values less than(to_date('2023-01-01', 'YYYY-MM-DD')),
  partition p_other values less than(MAXVALUE)
) enable row movement;

2、插入测试数据

insert into pt_range_test2 (pid, pname, create_date) values (1, '瑶瑶', TO_DATE (
			'2020-02-12 15:56:12',
			'SYYYY-MM-DD HH24:MI:SS'
		));
insert into pt_range_test2 (pid, pname, create_date) values (1500, '倩倩', TO_DATE (
			'2022-02-12 15:56:12',
			'SYYYY-MM-DD HH24:MI:SS'
		));
insert into pt_range_test2 (pid, pname, create_date) values (2, '优优', TO_DATE (
			'2021-02-12 15:56:12',
			'SYYYY-MM-DD HH24:MI:SS'
		));

3、验证数据是否存储到各个分区

select 'p_2019' 分区名, t.* from pt_range_test2 partition (p_2019) t union all
select 'p_2020' 分区名, t.* from pt_range_test2 partition (p_2020) t union all
select 'p_2021' 分区名, t.* from pt_range_test2 partition (p_2021) t union all
select 'p_2022' 分区名, t.* from pt_range_test2 partition (p_2022) t;

二、按季度分区 :

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

---

总结

如果此篇文章有帮助到您, 希望打大佬们能关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!

参考1

参考2