In Oracle 11g, there is now support for virtual column based partitioning. So you should be able to add a virtual column to your table and have that use the to_char function to extract the day of the week -
day_of_week AS TO_CHAR(my_date,'DAY')
Then, you should be able to just use range or list partitioning on this virtual column to create your day of the week partitions. The ILM Assistant only supports partitioning by DATE columns, so this partitioning scheme would not work with the Assistant.
Thanks for your reply.
Unfortunately, it's an Oracle database version 10g, not 11g.
I was wondering if it's possible to have many values when partitioning by range, for example....
Partition by range (date)
( Partition monday Values ('02-JUN-2008', '09-JUN-2008', '16-JUN-2008') Tablespace t1,
Partition tuesday Values ('03-JUN-2008', '10-JUN-2008', '17-JUN-2008') Tablespace t2,
Is this possible? Thanks.
I am not aware of any such capabilities. You could partition by LIST, but that would require you to specify every valid date in the partitioning clause for each partition. That may be feasible if you have only have a short time period or could write a script to generate the clauses.