MAINTAINING PARTITIONS [message #459039] |
Thu, 03 June 2010 02:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mohley_s
Messages: 10 Registered: January 2010 Location: bangalore, india
|
Junior Member |
|
|
Hi All,
I am studying
Oracle® Database
VLDB and Partitioning Guide
11g Release 2 (11.2),
can anyone explain me the error
ORA-14767: Cannot specify this interval with existing high bounds
by giving an example other than given in the above mentioned document.
the example given in the document is as follows:
To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
PARTITION p_before_2007 VALUES LESS THAN
(TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);
ALTER TABLE transactions
SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');
ORA-14767: Cannot specify this interval with existing high bounds
Request:
please do not take the partiton interval as 1 DAY for your example.
thanks,
Mohley
|
|
|
Re: MAINTAINING PARTITIONS [message #459041 is a reply to message #459039] |
Thu, 03 June 2010 02:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> CREATE TABLE transactions
2 ( id NUMBER
3 , transaction_date DATE
4 , value NUMBER)
5 PARTITION BY RANGE (transaction_date)
6 INTERVAL (NUMTODSINTERVAL(1,'DAY'))
7 (
8 PARTITION p_before_2007 VALUES LESS THAN
9 (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
10 );
Table created.
SQL> ALTER TABLE transactions
2 SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH');
SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH')
*
ERROR at line 2:
ORA-00907: missing right parenthesis
SQL> ALTER TABLE transactions SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));
Table altered.
SQL> @v
Version Oracle : 11.2.0.1.0
It works for me.
Regards
Michel
[Updated on: Thu, 03 June 2010 02:55] Report message to a moderator
|
|
|
Re: MAINTAINING PARTITIONS [message #459043 is a reply to message #459041] |
Thu, 03 June 2010 03:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
mohley_s
Messages: 10 Registered: January 2010 Location: bangalore, india
|
Junior Member |
|
|
HI Michel,
While pasting the code by mistake I left that parenthesis, that code is working for me also, but not according to the document. So, is it a mistake made by the document author?
Also, can u please explain me a situation in which i will get that error.
thanks & regards,
Mohley
|
|
|
Re: MAINTAINING PARTITIONS [message #459053 is a reply to message #459043] |
Thu, 03 June 2010 04:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval.
Quote:VALUES LESS THAN TO_DATE('01-JAN-2007','dd-MON-yyyy')
I think it is at the boundary of month.
Regards
Michel
|
|
|