分区字段VALUES LESS THAN时间格式及转换

分区字段的属性TIMESTAMP(3)或date，可以如下
alter table XX add PARTITION p01 VALUES LESS THAN (to_date('2003-1-1','yyyy-mm-dd'))
CREATE TABLE customer701
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );

CREATE TABLE customer702
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       TIMESTAMP(3)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );



alter table XX add PARTITION p01 VALUES LESS THAN (timestamp '2016-08-01 00:00:00.000')
CREATE TABLE customer7012
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       DATE
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (timestamp '2016-08-01 00:00:00.000')
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );

CREATE TABLE customer7022
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       TIMESTAMP(3)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (timestamp '2016-08-01 00:00:00.000')
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );



分区字段的属性varchar2（20),可以如下
alter table XX add PARTITION p01 VALUES LESS THAN (to_date('2004-1-1','yyyy-mm-dd'))
CREATE TABLE customer703
  ( cust_name      varchar2(20)
  , cust_id       NUMBER
  , time_id       varchar2（20)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('2010-04-01','yyyy-mm-dd'))
, partition sales_q_other VALUES LESS THAN(maxvalue)
 );

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2143425/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2143425/