日期类型分区表：range为date类型或者非date类型

按照range列是date类型 创建分区表命令

create table part_table(id number,stat_date date)
partition by range (stat_date)
(
partition p1 values less than (to_date('2012-01-09','yyyy-mm-dd')),
partition p2 values less than (to_date('2012-01-10','yyyy-mm-dd')),
partition p3 values less than (to_date('2012-01-11','yyyy-mm-dd'))
)

 

查询表分区信息，表名、分区名。

select table_name,partition_name from user_tab_partitions where table_name='PART_TABLE' order by table_name,partition_name;

 

range列为非date类型创建分区表；

create table part_table(id number,stat_date varchar2(100))

partition by range (stat_date)

(

partition p1 values less than ('2012-01-09'),

partition p2 values less than ('2012-01-10'),

partition p3 values less than ('2012-01-11')

)

 

插入数据，然后查询是否插入到争取的分区中

insert into part_table values(1,'2012-01-08');
select * from part_table partition(p1);
ID STAT_DATE
---------------------- ------------------

1 2012-01-08


insert into part_table values(1,'2012-01-09');
select * from part_table partition(p2);
ID STAT_DATE
---------------------- ----------------------

1 2012-01-09


insert into part_table values(1,'2012-01-10');
select * from part_table partition(p3);
ID STAT_DATE
---------------------- ----------------------

1 2012-01-10

都正确插入，成功！