Oracle SQL Table Purging (Like a logrotate for databases!)

[jasonmanley87]

Hello. In a corporate environment, we are running Oracle SQL (11g) and our disk space is getting filled up.

We have a table called 'LOG' (yes, log sounds like a reserved word in SQL, making this all the more fun). I'm trying to delete all entries older than three months, and at the same time I cannot do all the deleting at the same time, given that it will lock the DB. The time I am sorting by is a timestamp(9) , not the normal easy to use datetime datatype, so it looks like all my attempts to select from older than three months using every function I could google for about this will not work.

Also, how would I set up such a script to delete only 1000 entries at a time, and loop that?
Thank you.

[Rubberman]

It will be slower, but Oracle has an option you can set in a session connection that will allow each row insert/update/delete to be its own transaction, so to speak. You can also perform a range delete that is scoped within a transaction, limiting the delete to N records (such as 1000 or less). That should not escalate to a table lock, and the rollback segment should not cause an out-of-disc-space error. We have been dealing with a similar issue in our MySQL databases in production, and these were the means we used to solve that problem.