Temporary tablespace 11g

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

Leave a Reply

Your email address will not be published. Required fields are marked *