11G数据表按天分区

11g interval分区，按天分区，需要用到函数numtodsinterval。

 

create table T_PAR

(

dt date

)

partition by range (dt)

interval(numtodsinterval(1,'day'))

(

PARTITION SYS_001 VALUES LESS THAN (TO_DATE('20110501','yyyymmdd'))

);

 

###################################################################

 

numtodsinterval(<x>,<c>),x是一个数字,c是一个字符串,

 

表明x的单位,这个函数把x转为interval day to second数据类型

常用的单位有 ('day','hour','minute','second')

example

SQL> select sysdate,sysdate+numtodsinterval(3,'hour') as res from dual;

SYSDATE             RES

------------------- -------------------

2007-09-05 01:45:34 2007-09-05 04:45:34

numtoyminterval与numtodsinterval函数类似,将x转为interval year to month数据类型

常用的单位有'year','month'

example

SQL> select sysdate,sysdate+numtoyminterval(3,'year') as res from dual;

SYSDATE             RES

------------------- -------------------

2007-09-05 01:54:53 2010-09-05 01:54:53