View/SQL for temp segment usage

[grrickar]

If I would like to look at which session is currently using temporary segments, which view would I use? I did a select * from V$sort_usage but I am not sure what each of the columns represents. My tablespace is the same size on both of the databases (which are identical), but one of the databases shows the temp tablespace as being full. I want to find out what/who is holding those temp segments, and furthermore find out why the usage has not dropped in days(which it should do when the process/SQL statement completes, correct?) Could an orphaned SQL statement cause the temp tablespace to fill up and not deallocate temp segments?

[tamilselvan]

Try this:

SELECT s.username , u.TABLESPACE ,
u.segtype ,u.blocks, u.segblk#
FROM v$session s, v$sort_usage u
WHERE s.saddr = u.session_addr;

[grrickar]

I am running 8.1.6 on NTS, and I used a similar query: select a.username, b.contents, b.extents from v$session a, v$sort_usage b where a.saddr = b.session_addr; and the query returned no rows. Therefore it would seem that nothing is performing a large sort. I cannot explain why the tablespace is 2GB! and it is completely used (99.7%). It has been this way for weeks. No objects were created in the temp tablespace, so I would have guessed it could only be temp segments allocated taking up that much space. My concern now is when someone tries to do a sort, they may not be able to if the space is all used up. What can I check?

[jmodic]

You should not worry at all. If temp tablespace is configured as it should be it would yield simmilar picture as you are getting. In temp tablespace extents should not get deallocated after the sorting is done (so implicit or explicit coalescing on temp tablespace should never occure).

When sorting is over, the extents remain there and are available for subsequent sorts. Only when no "free" extents (that is, extents that are not in used by current sorts) are available will Oracle allocate next extents. So for example, if one of your create_a_realy_big_index commands allocate 2GB woth of temp extents, all those extents remai available for next sorts. Only when you create_an_even_bigger_index or perform tons of concurent sorts some new temp extents will get allocated there.

This saves the need for quite costy "allocate new extent" operation and dramaticaly increase the efficiency of sorts.