This article presents new features of 11g for temporary tablespaces.

• Locally managed TEMP tablespaces can be shrink online (useful after big sorts)
• Shrink can be done on TABLESPACE or TEMPFILE level

--shrink tablespace to smallest possible size
ALTER TABLESPACE temp SHRINK SPACE;

--shrink tablespace and keep only 100M
ALTER TABLESPACE temp SHRINK KEEP 100M;

--shrink tempfile to smallest possible size
ALTER TABLESPACE temp SHRINK TEMPFILE 'temp01.dbf';

--shrink tempfile and keep only 100M
ALTER TABLESPACE temp SHRINK TEMPFILE 'temp01.dbf' KEEP 100M;

• New view DBA_TEMP_FREE_SPACE columns:
  • TABLESPACE_NAME – temporary tablespace name
  • TABLESPACE_SIZE – total size in bytes
  • ALLOCATED_SPACE – size in bytes for (allocated space in use) and (allocated space for reuse)
  • FREE_SPACE – size in bytes for (allocated space for reuse) and (unallocated space)
• Temporary tablespace name can be specified during creation of global temporary tables

CREATE GLOBAL TEMPORARY TABLE test(id number)
ON COMMIT DELETE ROWS
TABLESPACE temp1; --this is new option

Example shrink TEMP tablespace

--check space size
select * from dba_temp_free_space;
/*
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                 503316480       503316480  497025024
*/

--shrink tablespace to 40M
alter tablespace temp shrink space keep 40M;

--check space size
select * from dba_temp_free_space;
/*
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  42991616         6291456   36700160 
*/

--shrink tempfile to 30M
alter tablespace temp shrink 
tempfile '+DATA/ora11g/tempfile/temp.264.790548041' keep 30m;

--check space size
select * from dba_temp_free_space;
/*
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  32497664         6283264   26214400 
*/

--shrink to smallest size
alter tablespace temp shrink space;

--check space size
/*
select * from dba_temp_free_space;
TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                  12574720         6283264    6291456 
*/

Have a fun 🙂

Tomasz