Does anyone know where I can find extensive info/examples of this? I've checked all my Oracle books and MetaLink with negative results. My project is employing this scheme, but I do not yet have the DDL for it available to me. I would like to better familiarize myself with it. All I do know is what's described in the Oracle Press 24x7 book, Chapter 6, top of page 259. The scheme is used to routinely purge old partitions and roll in new ones for partitioning based on windows of inclusive dates. Thanx for any help you can give me on this!
I recently implemented a project like this. Basically, we wanted to keep 1 year of data online with a month in each partition. However, instead of re-using partitions, I just maintained 12 partitions.
For example, in january my table would have 12 partitions:
Then, when it came time to "roll" the partitions, I dropped PART_2001_01 and added PART_2002_01. The beauty of this method is that all the partition maintenance can be done via a package.
I found that doing this requires monthly maintenance, and because youa re altering the table, you invalidate any packages/triggers against it.
You should test this first.
I also recommend that you MAY not want to put the year in the partition... only put PART_JAN, PART_FEB, etc.
This way you don't have to drop and add partitions, just clean out the partition (truncate partition) before the month cycles around again. Of course this limits you 2 11 months of history, but if you have a date column in your tables, you still could clean out only those dates from over 11 months ago.
Decide on how much maintenance you want, and if things get invalidated.
you may add a column (such as part_mnth number(2)) which is partitioned. you can write a trigger to insert/update this column whenever records are added/updated.
note: Keeping as number field has some advantage, becaz you can easily add, substrat, mod (useful when u automate the truncation of old partitions)
Also, u may need some sort of job queue/scheduled job to purge/truncate the old partitions whenever the date rolls back to the next year.
Click Here to Expand Forum to Full Width