DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: temp tablespace free space

  1. #1
    Join Date
    Dec 2007
    Posts
    82

    temp tablespace free space

    The temp tablespace always fills up and Oracle does not clear it out. If I expand the size of it, it still fills up. Even if all processes are finished, Oracle does not clear it out. Then I have programs fail with the "Unable to extend" error on the temp tablespace. Could anyone suggest me how to overcome this?. thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    When ever user process requires memory which is not available it uses temp segment. For example if you use order by, group by,sum, min, max etc additional processing requires on the result set returned.

    You can not do much to solve this issue. how ever you may check the code and ask the developer if they need this much data to be processed.

    BTW how much is your sort_area_size and sort_area_retained_size and is Autoextend ON?
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Dec 2007
    Posts
    82
    sort_area_size =0
    sort_area_retained_size =65536
    and Autoextend ON

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by mdvreddy
    The temp tablespace always fills up and Oracle does not clear it out. If I expand the size of it, it still fills up. Even if all processes are finished, Oracle does not clear it out. Then I have programs fail with the "Unable to extend" error on the temp tablespace. Could anyone suggest me how to overcome this?. thanks in advance
    Lets go step-by-step.

    First, temp tablespace is supposed to show you the HWM; it doesn't mean all space under the HWM is in use, space is allocated which is great for performance purposes... Oracle doesn't have to go and allocate/deallocate space. That been said, temp tablespace showing a great deal of space allocation is not an issue, this is the expected behavior.

    Second, when you get an "unable to extend" kind of error then you have a problem to be solved. Your temp space is certainly too small for your level of activity either because of it was set up too small or because of your queries are not properly fine tuned, therefore are using more temp space that needed.

    I would monitor temp space and check which query/queries are actually eating your space up; once you know that piece of information you can certainly determine if more temp space is actually needed or if you have to work on your queries.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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


Click Here to Expand Forum to Full Width