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.
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博客 ” ，链接：https://blog.itpub.net/30126024/viewspace-2146384/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
LOB、SecureFiles LOBs
请登录后发表评论
登录
全部评论
