Re: Table partitioning problem

Omi wrote:
> Hello there,
>
> I have a table in my application which can get very large. I'm using
> 9iR2 on Windows.
>
> The table has a DATE field, and due to the nature of the queries I run
> on the table, it makes sense for the table to have 31 partitions, one
> for every day of the month.
>
> However, I'm unable to create the table with the following SQL:
>
> CREATE TABLE test
> (
> timestamp DATE,
> user_id NVARCHAR2(200)
> )
> PARTITION BY RANGE (to_char(timestamp, 'DD'))
> (
> partition test_1 values (1),
> partition test_2 values (2),
> partition test_3 values (3),
> partition test_4 values (4),
> partition test_5 values (5),
> partition test_6 values (6),
> partition test_7 values (7),
> partition test_8 values (8),
> partition test_9 values (9),
> partition test_10 values (10),
> partition test_11 values (11),
> partition test_12 values (12),
> partition test_13 values (13),
> partition test_14 values (14),
> partition test_15 values (15),
> partition test_16 values (16),
> partition test_17 values (17),
> partition test_18 values (18),
> partition test_19 values (19),
> partition test_20 values (20),
> partition test_21 values (21),
> partition test_22 values (22),
> partition test_23 values (23),
> partition test_24 values (24),
> partition test_25 values (25),
> partition test_26 values (26),
> partition test_27 values (27),
> partition test_28 values (28),
> partition test_29 values (29),
> partition test_30 values (30),
> partition test_31 values (31)
> );
>
> I get syntax errors on the PARTITION BY RANGE line. It seems to expect
> *only* a fieldname there.
>
> How do I create a table like this?
>
> Thanks,
> Omi

Mind your DATA TYPES
Strictly speaking "(1)" is NOT a VARCHAR2

either
PARTITION BY RANGE (TO_NUMBER(to_char(timestamp, 'DD'))) or enclose the values with single quote marks partition test_31 values ('31') Received on Fri Apr 16 2004 - 19:22:44 CDT