| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: range partition on char column
On Fri, 07 Jul 2006 10:49:15 +0200, "mario" <xnazsco_at_tiscali.it>
wrote:
>Hi all,
>I'm using 9.2.0.1 on Solaris machine.
>I need to create a range partition table
>usign a CTAS syntax. The columns I have to
>use for range partition is a char(10) storing
>a date in the format yyyy-mm-dd (please don't
>ask me WHY!!! :) )
>
>I know that when we use a function to casting a
>datatype to another one, the order results can be
>different from the one aspected.
>But I've made some test, and the cast should work
>fine..but obviously it _does_ not work.
>
>following the scenario:
>
>original table:
>
>CCID NOT NULL NUMBER(10)
>CDATE NOT NULL CHAR(10)
>............ other columns here ................
>
>new table (the one I should create partitioned)
>
>CREATE TABLE NEW
> TABLESPACE TABLE_AREA
> PARALLEL
> NOLOGGING
> PARTITION BY RANGE (cdate)
> (partition q1_2003 values less than (to_Date('2003-05-01','yyyy-mm-dd')),
> partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),
> partition q3_2003 values less than (to_Date('2004-01-01','yyyy-mm-dd')),
> partition q1_2004 values less than (to_Date('2004-05-01','yyyy-mm-dd')),
> partition q2_2004 values less than (to_Date('2004-09-01','yyyy-mm-dd')),
> partition q3_2004 values less than (to_Date('2005-01-01','yyyy-mm-dd')),
>....... other partitions here ......................................
> partition extra values less than (maxvalue))
> AS SELECT * FROM OLD WHERE 1=2;
>
>
>When I try to create the table I receive the error:
>
>partition q2_2003 values less than (to_Date('2003-09-01','yyyy-mm-dd')),
> *
>ERROR at line 7:
>ORA-14037: partition bound of partition "Q2_2003" is too high
>
>
Hi Mario,
On a 9.2.07 on Win32 this error could not be reproduced : SQL> create table old (CCID NUMBER(10),CDATE CHAR(10));
Table Created
QL>CREATE TABLE new
PARTITION BY RANGE (cdate)
(partition q1_2003 values less than
(to_Date('2003-05-01','yyyy-mm-dd')),
partition q2_2003 values less than
(to_Date('2003-09-01','yyyy-mm-dd')),
partition q3_2003 values less than
(to_Date('2004-01-01','yyyy-mm-dd')),
partition q1_2004 values less than
(to_Date('2004-05-01','yyyy-mm-dd')),
partition q2_2004 values less than
(to_Date('2004-09-01','yyyy-mm-dd')),
partition q3_2004 values less than
(to_Date('2005-01-01','yyyy-mm-dd')),
partition extra values less than (maxvalue))
AS SELECT * FROM old WHERE 1=2;
Table created
SQL> Please post the *complete statement*, as I believe the error is to be found here :
>....... other partitions here ......................................
If one of the partition definition here, as I believe it does,
conflicts with
" partition q2_2003 values less
than(to_Date('2003-09-01','yyyy-mm-dd'))."
Oracle can only tell that there is a conflict and point to one of the
parts in the conflict, presumably the first one, which in this case is
:
" partition q2_2003 values less
than(to_Date('2003-09-01','yyyy-mm-dd'))."
 |
 |