Temporary tablespace

[shrikant.patil]

Hi All,

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

[gandolf989]

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.

[ixion]

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?

[davey23uk]

temporary tablespaces will always show as full

[dbasan]

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.

[ixion]

See: v$tempseg_usage and V$temp_extent_map.

[slimdave]

Set up as Locally managed, with uniform extent sizes.

They always are: http://download-east.oracle.com/docs...3.htm#i2063392

[ixion]

They always are: http://download-east.oracle.com/docs...3.htm#i2063392

Thanks for correcting me. Sometimes I relapse back to Oracle 7

[Bore]

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