Patitionated Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Patitionated Tables

  1. #1
    Join Date
    Mar 2001
    Posts
    188
    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

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  3. #3
    Join Date
    Mar 2001
    Posts
    188
    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

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Somewhere in the back of my mind I remember that you can't partition on a calculated value. Can anybody else confirm or deny?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  6. #6
    Join Date
    Mar 2001
    Posts
    188
    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]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width