Find the answer to your Linux question:
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.
  1. #1
    Just Joined!
    Join Date
    Apr 2011
    Posts
    10

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


    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.

  2. #2
    Linux Guru Rubberman's Avatar
    Join Date
    Apr 2009
    Location
    I can be found either 40 miles west of Chicago, in Chicago, or in a galaxy far, far away.
    Posts
    11,397
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •