Just stumbled on the shrink command for the temp tablespace, I normally just do a reisze of the datafile but just tested the command and it works great!
Test
SYS@testdb1> Select tablespace_name, file_name, bytes/1024/1024 “Size MB”,AUTOEXTENSIBLE, MAXBYTES/1024/1024 “Auto MB” from dba_temp_files;
TABLESPACE_NAME FILE_NAME Size MB AUT Auto MB
—————————— —————————————————————– ———- — ———-
TEMP /wload/test1/app/oracle/u01/oradata/testdb1/temp.dbf 400 YES 2000
Now shrink the tablespace
SYS@testdb1> alter tablespace TEMP SHRINK SPACE KEEP 300M;
Tablespace altered.
Size of the tablespace now 300M
SYS@testdb1> Select tablespace_name, file_name, bytes/1024/1024 “Size MB”,AUTOEXTENSIBLE, MAXBYTES/1024/1024 “Auto MB” from dba_temp_files;
TABLESPACE_NAME FILE_NAME Size MB AUT Auto MB
—————————— —————————————————————– ———- — ———-
TEMP /wload/test1/app/oracle/u01/oradata/testdb1/temp.dbf 300 YES 2000
