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

Thread: Temporary tablespace

  1. #1
    Join Date
    Jun 2007
    Posts
    1

    Temporary tablespace

    Hi All,

    can somebody help ..
    how to reduce fragementation of temporary tablespace ..
    bcause always my temp tableapace gets full ...

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by shrikant.patil
    Hi All,

    can somebody help ..
    how to reduce fragementation of temporary tablespace ..
    bcause always my temp tableapace gets full ...
    It always helps to start by posting in the right forum.

    The first question is how do you know you have fragmentation? Secondly, Oracle does not necessarily free space in the temp tablespace. Don't expect TEMP to shrink even if its not in use.

  3. #3
    Join Date
    Jun 2006
    Posts
    259
    Set up as Locally managed, with uniform extent sizes.

    Being full is not the same as fragmentation. If its full you can increase the file size. Are you getting an oracle error?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    temporary tablespaces will always show as full

  5. #5
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    One of my Temp tablespace is always full 99%. Until I get error like unable to extend i would generally not increase the file size.

    I think you can worry about other db issues.

    Monitor the temp space activity and then you can come to conclusion.

  6. #6
    Join Date
    Jun 2006
    Posts
    259
    See: v$tempseg_usage and V$temp_extent_map.

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by ixion
    Set up as Locally managed, with uniform extent sizes.
    They always are: http://download-east.oracle.com/docs...3.htm#i2063392
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by slimdave
    Thanks for correcting me. Sometimes I relapse back to Oracle 7

  9. #9
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Support Davey23UK it's normal to be full.
    Let me try to clarify - when the first sort operation appear, it creates temp segment into the temp tablespace. The temp segment gets extents when needed to increase, but when the sort operation finishes, the extents do not gets released to the tablespace, since there are no other segments in it and so, no one could possibly need this space.
    To summarize, there usually is enough free space but INTO the temp segment. The tablespace seems to be full, since most of the space is occupied by the temp segment however.
    look at the v$tempseg_usage and V$temp_extent_map as IXION said, to find out how much space is really used

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