Results 1 to 2 of 2
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 ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
- 07-13-2012 #1
- Join Date
- Apr 2011
Oracle SQL Table Purging (Like a logrotate for databases!)
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?
- 07-15-2012 #2
- Join Date
- Apr 2009
- I can be found either 40 miles west of Chicago, in Chicago, or in a galaxy far, far away.
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.
Just remember, Semper Gumbi - always be flexible!