Oracle 11g分区学习

1.表分区语法

1.1 按年创建分区表

create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, ‘year’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));

1.2 按月创建分区表

–按月创建分区表
create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, ‘month’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));

1.3 按天创建分区表

create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, ‘day’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));

1.4 按周创建分区表

create table TEST
(
ID number(20) not null,
NAME varchar2(1000),
CREATE_TIME date
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, ‘day’))
(partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)));
备注：
partition part_t01 values less than(to_date(‘2019-04-01’, ‘yyyy-mm-dd’)) 表示表示小于 2019-04-01 的都放在 part_t01 分区表中。

2.查看分区表信息

按日期添加数据，oracle 会自动添加分区。

–查询当前表有多少分区
select table_name,partition_name from user_tab_partitions where table_name=‘TEST’;
–查询这个表的某个（part_t01）里的数据
select * from TEST partition(part_t01);

参考资料
https://www.cnblogs.com/yuxiaole/p/9809294.html