Temporary space is not freed when it is no longer being used. The allocated space is available to be used by the next process that needs it. However, the extents can become so fragmented that a process which needs a lot cannot get what it needs.
There are at least two ways to clean up your temp tablespace:
1) Shutdown the instance. Bring it back up. Alter the temp tablespace to coalesce free space.
2) Alter the temp tablespace offline. Wait a few minutes. Alter it back online. Alter it to coalesce free space.
Thanks for your replies & info. However, Ratcheer, you can not coalesce a temporary tablespace which is locally managed.
sambhavan, v$temp_space_header shows free blocks 0. I have 12 GB in this TS. I agree that the space allocated is not freed and all space shows used up.
However, I wanted to know if there is any way to check, how much is actually used at the point in time by all sessions and how to know that it is time for its extention. One can wait till sessions start giving frequent errors, but that is hardly a good choice.
-- Determine current status of temp space usage
select CURRENT_USERS, TOTAL_EXTENTS, USED_EXTENTS, FREE_EXTENTS,
ADDED_EXTENTS, EXTENT_HITS, FREED_EXTENTS, FREE_REQUESTS, MAX_SIZE,