interval分区时需要注意月末日期的影响

使用INTERVAL时需要注意月末日期的影响，实际上如果使用大于28号的日期来指定分区上界，就会得到一个错误：
ORA-14767: Cannot specify this interval with existing high bounds

SQL> CREATE TABLE orders (order_no NUMBER,producer_id NUMBER,time_id DATE) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('2012-12-31', 'YYYY-mm-dd')));
CREATE TABLE orders2 (order_no NUMBER,producer_id NUMBER,time_id DATE) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('2012-12-31', 'YYYY-mm-dd')))
*
ERROR at line 1:
ORA-14767: Cannot specify this interval with existing high bounds


SQL> CREATE TABLE orders (order_no NUMBER,producer_id NUMBER,time_id DATE) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-mm-dd')));
Table created.


SQL> CREATE TABLE orders5 (order_no NUMBER,producer_id NUMBER,time_id DATE) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('2012-12-29', 'YYYY-mm-dd')));
CREATE TABLE orders5 (order_no NUMBER,producer_id NUMBER,time_id DATE) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('2012-12-29', 'YYYY-mm-dd')))
*
ERROR at line 1:
ORA-14767: Cannot specify this interval with existing high bounds


SQL> CREATE TABLE orders5 (order_no NUMBER,producer_id NUMBER,time_id DATE) PARTITION BY RANGE (time_id) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) (PARTITION p0 VALUES LESS THAN (TO_DATE('2012-12-28', 'YYYY-mm-dd')));
Table created.

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

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