Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!news.glorb.com!news.moat.net!border1.nntp.sjc.giganews.com!border2.nntp.sjc.giganews.com!nntp.giganews.com!news1.optus.net.au!optus!newsfeeder.syd.optusnet.com.au!news.optusnet.com.au!not-for-mail
Date: Sat, 17 Apr 2004 13:02:15 +1000
From: "Howard J. Rogers" <hjr@dizwell.com>
User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: Table partitioning problem
References: <e01d2feb.0404161604.5335c038@posting.google.com>
In-Reply-To: <e01d2feb.0404161604.5335c038@posting.google.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 66
Message-ID: <40809e42$0$6018$afc38c87@news.optusnet.com.au>
NNTP-Posting-Host: 203.164.5.240
X-Trace: 1082170947  6018 203.164.5.240
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:259677

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

By reading the documentation and discovering the fact that range 
partitioning requires the syntax "values LESS THAN (x)".

You are implying "EQUAL TO", and that is not permissible.

HJR