Re: more temp tablespace problems

Glen A Stromquist <glen_stromquist_at_nospam.yahoo.com> wrote in message news:<5oUt9.82573$wU3.3707464_at_news0.telusplanet.net>...
> What would cause a temp tablespace to not clear when boucing the database?
> My problem db that blows the temp up to 30+ gb before failing still has 13
> gb of space used when the database is restarted.
>
> This seems to be a very strange problem, once when I bouced it the temp
> tablespace showed as empty, but would not let me shrink it because it said
> there was still data in it. I ended up creating another smaller datafile
> for it and taking the 1st one offline.
>
> ideas?
>
> TIA
Which release of Oracle? If you defined the tablespace as Temporary, it will create a Temp segment on First sort operation. It allocates the extents to this temp segment for sort operation for different sessions. It never deallocate the extents until instance is bounced but it reuses the extents , if extents is currently not being used. V$sort_usage and v$sort_segment will give you all the information about these segements and its extents.

So you might see tablespace as FULL ( Because of very large SORT operation at some point ) but actually it might still have lots of extents that can be reused. Above view will give you enough information @ how many sessions are currently using the temp segment, how many extents they are using and also High water mark ( Max extents ever used ).

Hope this helps. Received on Thu Oct 24 2002 - 23:08:02 CDT