In Oracle 11.1 and above versions you can shrink TEMP tablespace and/or Tempfiles.
With dba_temp_free_space view you can check temp tablespace space usage:
SQL> SELECT * FROM dba_temp_free_space;
With command:
ALTER TABLESPACE <temp_tablespace_name> SHRINK SPACE KEEP <size_for_temp_tablespace>M;
You can shrink temp tablespace to certain size.
For example to shrink temp tablespace to 40M:
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;
And if you want to shrink certain tempfile you can do it with following command:
ALTER TABLESPACE <temp_tablespace_name> SHRINK TEMPFILE <'path_to_tempfile_and_tempfile_name'> KEEP <size_for_tempfile>M;
For example to shrink test_temp01.dbf tempfile to 40M :
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/testdb/test_temp01.dbf' KEEP 40M;
If you don't set the KEEP clause in the end of the shrink clause then tablespace or tempfile will be shrinked as small as possible (1M).
Thanks for sharing. It will help me to troubleshoot TEMPORARY Tablespace issues.
ReplyDelete