SQL> CREATE TABLE test(
2 RUN_DATE DATE,
3 MY_NBR NUMBER(4)
4 )
5 PARTITION BY RANGE (RUN_DATE)
6 INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
7 (
8 PARTITION data_p1 VALUES LESS THAN (TO_DATE('01/29/2017', 'MM/DD/YYYY'))
9 );
CREATE TABLE test(
*
ERROR at line 1:
ORA-14767: Cannot specify this interval with existing high bounds
SQL>
SQL> CREATE TABLE test(
2 RUN_DATE DATE,
3 MY_NBR NUMBER(4)
4 )
5 PARTITION BY RANGE (RUN_DATE)
6 INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
7 (
8 PARTITION data_p1 VALUES LESS THAN (TO_DATE('01/28/2017', 'MM/DD/YYYY'))
9 );
Table created.Creating a range partitioned table with a date type partitioning column and a month interval must have a starting partition that has a day < 29 or it gets an ORA-14767 error.
The error message “Cannot specify this interval with existing high bounds” is not helpful. How about something like “Need a day of the month that exists in every month”? February only has 28 days most years so 28 is the max.
Bobby
Very interesting, we do monthly partitioning but always did less than the 1st of the month so never really thought about this. Makes perfect sense though now that you posted this as every possible scenario must be accounted for . Thanks for sharing.
Thanks Rich. I would not have seen this but we tried to make the first partition have a high value of 12/31/2017 and got the error. Figured I might as well document it.
Bobby
Glad you documented it, I am sure I would have been scratching my head one day with the same sort of error. It’s always the odd little things that get you even though as an afterthought it makes complete sense.