Prior to 11G if the DBA would like to reclaim space from the temporary tablespace, he/she could run into the below situation
14:00:38 SILVER> alter database tempfile 1 resize 2G;
alter database tempfile 1 resize 2G
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
From 11G onwards
Similar to online data space compact and shrink
14:01:53 SILVER> select tablespace_name,tablespace_size/(1024*1024*1024) "tablespace_size",
14:02:09 2 allocated_space/(1024*1024*1024) "allocated_space",
14:02:17 3 free_space/(1024*1024*1024) "free_space"
14:02:25 4 from dba_temp_free_space;
TABLESPACE_NAME tablespace_size allocated_space free_space
------------------------------ ---------------- --------------- ----------------
TEMP 8 .03 7.966796875
This is how the DBA can shrink the temp space
14:05:27 SILVER> alter tablespace TEMP shrink space keep 2G;
Tablespace altered.
One can also do the operation at level of the tempfile
14:05:27 SILVER> alter tablespace TEMP shrink tempfile '/u01/oradata/SILVER/temp01.dbf' keep 2G;