天天看點

oracle 精确到小數秒的timestamp類型分區表

CREATE TABLE TEST_CARD(ID NUMBER() NOT NULL,CREATEDATE   TIMESTAMP())
    PARTITION BY RANGE (CREATEDATE)(PARTITION P201104 VALUES LESS THAN (TIMESTAMP' 2011-04-01 14:30:30.3333'),PARTITION P201207 VALUES LESS THAN (TIMESTAMP' 2012-07-01 00:00:00'),PARTITION PMAX VALUES LESS THAN (MAXVALUE));

Table created.

SQL> insert into test_card values(,to_timestamp('2011-04-01 14:30:30.3000','yyyy-mm-dd hh24:mi:ss:ff'));

 row created.

SQL> select * from test_card partition(p201104);

    ID CREATEDATE
---------- ---------------------------------------------------------------------------
      -APR- . PM

SQL> insert into test_card values(,to_timestamp('2011-04-01 14:30:30.4000','yyyy-mm-dd hh24:mi:ss:ff'));

 row created.

SQL> select * from test_card partition(p201104);

    ID CREATEDATE
---------- ---------------------------------------------------------------------------
      -APR- . PM

SQL> select * from test_card partition(p201107);
select * from test_card partition(p201107)
                                  *
ERROR at line :
ORA-: Specified partition does not exist


SQL> select * from test_card partition(p201207);

    ID CREATEDATE
---------- ---------------------------------------------------------------------------
      -APR- . PM