This may be one for the gurus, or it could be big standard. Don't know.

I'm looking for some help in designing a database. The problem I have is related to data aging on Oracle instances. I can't find any help on it.

The problem I have is that I've got 3 categories of data, which is new/warm/old. Essentially, new is data which is 1 hour old. Warm is data which is kept for 24 hours, old is kept for 6-8 weeks.

So the data needs to age. I think obviously the mechanics of it, Is that I need to partition the data in 1 hour range perhaps, and have a pl/sql package to run every hour, that moves partitions about from new to old, like a sliding window mechanism.

Thanks for your help.