How to refresh Temporary tablespace

[Kishore]

Hi

My temporary tablespace is full. Now, no sorting is going on. Is it possible to refresh it.

[Halo]

Are you sure?? There might be sorting perofrmed by Oracle itself, if not by any other user. The only way to clean out the temp ts is to bounce the db.

[JDoyle]

Did you get some sort of error, or are you trying to free up disk space?
If the latter, temporary tablespaces do not drop extents after they've been used for sorts, but hang on to them to save dynamic allocation overhead for later sorts. So the size of the tablespace will reflect the largest sort(s) you've done in there. The tablespace may show as being full, but it has space available for new sorts.
You'd have to drop and recreate the TS to change storage parameters and datafile size.

[Kishore]

Yes, it has shown could not extent by 126K ..... May be some of the user might have done big sort and may be it failed. However, that error occured yesterday.

And the tablespace is full. However, now I am clear with it. Thanks

[JDoyle]

Is your temporary tablespace of temporary type? and is the datafile set to autoextend?
Select contents from dba_tablespaces where tablespace_name='temp';