When is temp tablespace been freed?
our temp tablespace is 2GB, and it is always full(100% used)
Could you tell me know
1) when temp tablespace is freed?
2) how to find out which users are using temp tablespace?
1) Extents in the temp tablespace are only freed when the database is shut down. It is normal, and desirable, for your temp tablespace to show as 100% full. Allocating extents is an expensive operation. Once Oracle allocates an extent in temp, it manages them internally, re-using them as required.
2) You can use v$sort_usage to see who is using temp tablespace. However, do not be surprised if noone is using temp even if it is 100% full.