Automatically enforcing a max database table size by dropping old rows

Question

I have an Oracle table which contains event log messages for an application. We don't want this table to grow out of control and eat up too much space, so we'd like to set a cap on the number of rows that it can contain to some big number like one million.

So we'd like to tell Oracle that when the table grows too large, it should delete the oldest rows to make space for new rows.

Is there any way to do this? I imagine we could do this with a trigger or by making all inserts with a stored procedure, but is there anything simpler?

EDIT: A couple of answers have suggested solutions that involve partitions. We do not currently partition this table, although we have the ability to do so if necessary. However, from looking into the matter, it seems that even if we partition the table, we'd still need for some kind of a scheduled job to drop the old partitions, etc. So we've decided to forgo partitions in favor of a scheduled job to check the row count and delete old rows as necessary once per day.

Thanks for the help, everyone.

Answer 1

Depending on your definition of simpler...

My bias would be to schedule a job that runs periodically (say, nightly) in order to delete the oldest rows. If you expect that you could generate a million event logs in a single day, you may want the job to run more frequently, but nightly is generally sufficient for most people. This has the advantage of being asynchronous so that you're not incurring the overhead of counting a million rows every time you do an insert to find out if you have to purge some data. On the other hand, it does potentially require some administration to set up the job.

Answer 2

Do you have the partitioning option?

If so, this is a good candidate for a rolling partition window. As the alert log ages, you can drop the oldest partition.

Answer 3

you can't tell Oracle to keep my table at a certain size. If this is news, partitioning might not be for you. Otherwise, I'd completely agree with Nick.

Remember the 1M is just a magic number that you've decided to keep, it's ok if it gets to 1.1M before an clean up occurs. If you do rolling partitions, you'll need to abandon your 1M number for a number of days to keep. You'll make, daily, weekly, whatever partitions and drop the oldest based on your criteria.

Add a comment to your question to indicate if you have partitioning.

Answer 4

If you only have a few thousand rows, sure a simple delete will work. But if you expect growth or have a lot of data already then partitioning is your best approach.

• Delete can be prone to Undo space errors.
• Delete can be prone to snapshot to old errors.
• Delete does not reduce the tables HWM (high water mark)
• Delete may take a while depending upon the data size.
• Delete generates a lot of redo (depending on how much data you actually delete).
• Dropping a partition generates very little redo.
• Partitioning can improve some queries where Oracle performs partition elimination.

I probably missed a few features etc., Partitioning will work great for dropping data. Its fast, will keep your data organized. And is scalable to large data volumes.