Patitionated Tables

[tomate]

Hey Gurus,

I can't partitionated the table by date. Can You help me

CREATE TABLE part
( stime, etc)
partition by range (to_char(stime,'DAY'))
(partition sun values equal then ('MONDAY'),
partition mon values equal then ('TUESDAY'),
partition tue values equal then ('WEDNESDAY'),
partition wen values equal then ('THURSDAY'),
partition thr values equal then ('FRIDAY'),
partition fri values equal then ('SATURDAY'),
partition sat values equal then ('SUNDAY'))

Regards
Thomas

[denevge]

You cannot use
'VALUES EQUAL THEN' when creating a range-partition
You have to use
'VALUES LESS THEN'

Regards
Gert

or if you have 9i, you can use list partitioning

[tomate]

Yes i know, this is may error.
I have write this but i get the following error

ORA-00907: missing right parenthesis

And i Know the description of the erorr but i can't see it. Now the statement again.

CREATE TABLE part
( stime DATE, etc. )
partition by range (to_char(stime,'DAY'))
(partition sun values less then ('MONDAY'),
partition mon values less then ('TUESDAY'),
partition tue values less then ('WEDNESDAY'),
partition wen values less then ('THURSDAY'),
partition thr values less then ('FRIDAY'),
partition fri values less then ('SATURDAY'),
partition sat values less then ('SUNDAY'))

Regards
Thomas

[marist89]

Somewhere in the back of my mind I remember that you can't partition on a calculated value. Can anybody else confirm or deny?

[denevge]

Just checked the 817 docs. It says

... PARTITION BY RANGE (column_list) ...

so indeed, we cannot partition a table on a calculated value
a table.

Regards
Gert

[tomate]

OK, now i take the column_list = stime
The problem is that i want to patitioned my table on the week days and in a book from me, there is the information that i can partitioned the table on a lot of time units.

Now my statment is:

CREATE TABLE part
( stime DATE, etc. )
partition by range (stime)
(partition sun values less than (to_date('MONDAY','DAY')),
partition mon values less than (to_date('TUESDAY','DAY')),
partition tue values less than (to_date('WEDNESDAY','DAY')),
partition wen values less than (to_date('THURSDAY','DAY')),
partition thr values less than (to_date('FRIDAY','DAY')),
partition fri values less than (to_date('SATURDAY','DAY')),
partition sat values less than (to_date('SUNDAY','DAY')))

So i get the following mistake:
ORA-01846: not a valid date of the week

I need a table partitioned of the week !!!!
Regards
Thomas


[Edited by tomate on 06-06-2001 at 12:22 PM]