关于oracle 11G 分区表新特性Interval

oracle 11G新引入的新特性Interval，可以自动根据入库需求创建分区，而不需人工干预。

------------------------月------------------------------

Create table intervalpart
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c3)
interval(numtoyminterval(1,'MONTH'))
(
partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
)；

------------------------年------------------------------

Create table intervalpart
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c3)
interval(numtoyminterval(1,'YEAR’))
(
partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
)；

------------------------天------------------------------

Create table intervalpart
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c3)
interval(numtodsinterval(1,'DAY‘))
(
partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
)；

------------------------秒------------------------------

Create table intervalpart
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c3)
interval(numtodsinterval(1,'SECOND‘))
(
partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
)；

注意红色部分，分成5各部分

interval (num to ds interval(1,'SECOND‘))

重要参数是第四个部分 也就是ds和ym，

d--day

s--second

y--year

m--month