nable to convert from RANGE to INTERVAL partition

[83]Hi Michel, Thanks for your help. Your suggestion ...... Jayan Kumar
[85]Thanks for the clarification. Am going to learn Ca...... Jayan

Subject: Unable to convert from RANGE to INTERVAL partition
Os info: Linux
Oracle info: 11.2.0.4.0
Error info: ORA-14767: Cannot specify this interval with existing high
bounds
Message: Hello All,
Am getting below error when am trying to convert the RANGE partition
into interval partition (alter table stat.). Is there a way to convert
without dropping existing partition data?
create table test_partition
(
stud_id number(3),
CREATION_DATE DATE not null
)
partition by range (CREATION_DATE)
(
partition test_part1 values less than (TO_DATE(' 2015-12-31 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

ALTER TABLE test_partition SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));

ORA-14767: Cannot specify this interval with existing high bounds

Note: Am not using MAXVALUE while declare the end date for the
partition.
Regards
Jayan


Subject: Re: Unable to convert from RANGE to INTERVAL partition
Message:
Your initial partition must match a bound of your interval:
SQL> create table test_partition
2 (
3 stud_id number(3),
4 CREATION_DATE DATE not null
5 )
6 partition by range (CREATION_DATE)
7 (
YYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
9 );

Table created.

SQL> ALTER TABLE test_partition SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));

Table altered.


Regards
Michel


Subject: Re: Unable to convert from RANGE to INTERVAL partition
Message: Hi Michel,
Thanks for your help. Your suggestion worked for me.
Since already I have the table with data, I used below ALTER statement
and it worked well.
16-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));

ALTER TABLE test_partition SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));

One more question. Even after I changed from RANGE to INTERVAL, why
USER_PART_TABLES showing my partition_type as RANGE?
Will it change when I actually inserted the data? Appreciate your
clarification.
Regards
Jayan


Subject: Re: Unable to convert from RANGE to INTERVAL partition
Message:
There is no 'INTERVAL' type in this view, interval partitioned tables
are included in RANGE type.
You can see it is actually an interval partitioned table with the
INTERVAL column:
SQL> select partitioning_type, interval from user_part_tables where table_name=u
pper('test_partition');
PARTITION INTERVAL
--------- ------------------------------
RANGE NUMTOYMINTERVAL(1,'MONTH')


Regards
Michel


Subject: Re: Unable to convert from RANGE to INTERVAL partition
Message: Thanks for the clarification. Am going to learn Cassendra to
know another dbms. Do you know any active forums to discuss?
Regards
Jayan