Saturday, January 5, 2013

Shrink TEMP Tablespace or Tempfile in Oracle 11.1 and above.

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).

1 comment:

  1. Thanks for sharing. It will help me to troubleshoot TEMPORARY Tablespace issues.

    ReplyDelete