Good Evening,

I have a few questions regarding temporary tablespace's efficiency with re-using previously used blocks in the temporary tablespaces. First, can Oracle 11g+ re-use previously used blocks? In other words, if a session needs 900MB of temporary tablespace in the one temporary tablespace with a maxsize of 1gB, completes sorting data at 10am. Then another session performs a sort that required 500MB, will it be able to re-use the temp space or is it possible for it to get an "unable to extend temp segment"?

Is there a command that we can use that will allow us to deallocate any previously used temp blocks? Will a "shrink space keep" perform that operation?

Thanks for your help,

John

select tablespace_name,
       tablespace_size / 1024 / 1024 sz, 
       allocated_space / 1024 / 1024 asp,
       free_space / 1024 / 1024 fsp
from dba_temp_free_space;

TABLESPACE_NAME  SZ   ASP  FSP  
TEMP             747  747  746 

alter tablespace temp shrink space keep 500M;

select tablespace_name,
       tablespace_size / 1024 / 1024 sz, 
       allocated_space / 1024 / 1024 asp,
       free_space / 1024 / 1024 fsp
from dba_temp_free_space;

TABLESPACE_NAME  SZ   ASP  FSP  
TEMP             500  1    499