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, 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.Sometimes, real fast is almost as good as real time.
Just remember, Semper Gumbi - always be flexible!