Temporary Tablespace in Oracle

Oracle introduced temporary tablespaces in Oracle 7.3

Temporary tablespaces are used to manage space for database sort and joining operations and for storing global temporary tables. For joining two large tables or sorting a bigger result set, Oracle cannot do in memory by using SORT_AREA_SIZE in PGA (Programmable Global Area). Space will be allocated in a temporary tablespace for doing these types of operations. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

Note that a temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up. A temporary tablespace contains schema objects only for the duration of a session.

Creating Temporary Tablespace in Oracle

From Oracle 9i, we can specify a default temporary tablespace when you create a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement.

e.g.

SQL> CREATE DATABASE oracular .....

DEFAULT TEMPORARY TABLESPACE temp_ts .....;

Oracle provides various ways of creating TEMPORARY tablespaces.

Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;

Example:

SQL> CREATE TABLESPACE TEMPTBS DATAFILE '/path/temp.dbf' SIZE 2048M

AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE EXTENT MANAGEMENT DICTIONARY;

Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;

Example:

SQL> CREATE TABLESPACE TEMPTBS DATAFILE '/path/temp.dbf' SIZE 2048M

AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING DEFAULT NOCOMPRESS ONLINE

TEMPORARY EXTENT MANAGEMENT DICTIONARY;

Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;
Examples:
SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE '/path/temp.dbf' SIZE 1000M
AUTOEXTEND ON NEXT 8K MAXSIZE 1500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M BLOCKSIZE 8K;

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS2 TEMPFILE '/path/temp2.dbf' SIZE 1000M
AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL BLOCKSIZE 2K;

The MAXSIZE clause will default to UNLIMITED, if no value is specified.
All extents of temporary tablespaces are the same size, so UNIFORM keyword is optional - if UNIFORM is not defined it will default to 1 MB.

Example using OMF (Oracle Managed Files):

SQL> CREATE TEMPORARY TABLESPACE temp;

Restrictions:

(1) We cannot specify nonstandard block sizes for a temporary tablespace or if you intend to assign this tablespace as the temporary tablespace for any users.

(2) We cannot specify FORCE LOGGING for an undo or temporary tablespace.

(3) We cannot specify AUTOALLOCATE for a temporary tablespace.

Tempfiles (Temporary Datafiles)

Unlike normal datafiles, temporary files are not fully allocated. When you create a tempfiles, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a tempfiles than to create a normal datafile.

Tempfiles are not recorded in the database's control file. This implies that just recreate them whenever you restore the database, or after deleting them by accident. You can have different tempfile configurations between primary and standby databases in dataguard environment, or configure tempfiles to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a tempfile from a database. Look at this example:

SQL> alter database tempfile 'tempfile_name' drop including datafiles;

//If the file was created as tempfile

SQL> alter database datafile 'tempfile_name' drop;

//If the file was created as datafile

Dropping temporary tablespace
SQL> drop tablespace temp_tbs;

SQL> drop tablespace temp_tbs including contents and datafiles;

If you remove all tempfiles from a temporary tablespace, you may encounter an error:

ORA-25153: Temporary Tablespace is Empty.

Use the following statement to add a tempfile to a temporary tablespace:

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/path/temp01.dbf' SIZE 512m AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

SQL> ALTER TABLESPACE temp_ts ADD TEMPFILE '+DATA' SIZE 512m AUTOEXTEND ON NEXT 500m MAXSIZE UNLIMITED;

alter tablespace temptbs rename to temptbs2;

Except for adding a tempfile, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).

To resize temporary files (in Oracle database):

alter database tempfile temp_file_name resize nK|M|G|T|P|E;

Locally managed temporary tablespaces have temporary datafiles (tempfiles), which are similar to ordinary datafiles except:

• You cannot create a tempfile with the ALTER DATABASE statement.
• You cannot rename a tempfile or set it to read-only.
• Tempfiles are always set to NOLOGGING mode.
• When you create or resize tempfiles, they are not always guaranteed allocation of disk space for the file size specified. On certain file systems (like UNIX) disk blocks are allocated not at file creation or resizing, but before the blocks are accessed.
• Tempfile information is shown in the dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE.

Note: This arrangement enables fast tempfile creation and resizing, however, the disk could run out of space later when the tempfiles are accessed.

Default Temporary Tablespaces
From Oracle 9i, we can define a default temporary tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

By default, the default temporary tablespace is SYSTEM. Each database can be assigned one and only one default temporary tablespace. Using this feature, a temporary tablespace is automatically assigned to users.

The following restrictions apply to default temporary tablespaces:
-DEFAULT TEMPORARY TABLESPACE must be of type TEMPORARY.
-DEFAULT TEMPORARY TABLESPACE cannot be taken off-line.
-DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.

To see the default temporary tablespace for a database, execute the following query:
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%TEMP%';

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:
SQL> CREATE USER scott TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

To change a user account to use a non-default temporary tablespace
SQL> ALTER USER user1 SET TEMPORARY TABLESPACE temp_tbs;

Assigning temporary tablespace group as default temporary tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;

Assigning temporary tablespace group to a user (same as assigning temporary tablespace to a user):

SQL> ALTER USER scott TEMPORARY TABLESPACE temp_grp;

All new users that are not explicitly assigned a TEMPORARY TABLESPACE will get the default temporary tablespace as its TEMPORARY TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the default temporary tablespace for the database.

select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Performance Considerations
Some performance considerations for temporary tablespaces:

• Always use temporary tablespaces instead of permanent content tablespaces for sorting & joining (no logging and uses one large sort segment to reduce recursive SQL and ST space management enqueue contention).
• Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed (i.e. use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space).
• Always use TEMPFILE instead of DATAFILE (reduce backup and recovery time).
• Stripe your temporary tablespaces over multiple disks to alleviate possible disk contention and to speed-up operations (user processes can read/write to it directly).
• The UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.

Monitoring Temporary Tablespaces

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP';

TABLESPACE_NAME FILE_NAME BYTES

----------------- -------------------------------- --------------

TEMP /../temp01.dbf 11,175,650,000

select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name;

SQL> select file#, name, round(bytes/(1024*1024),2) "Temp file SIZE IN MB's" from v$tempfile;

select ts#, sum(bytes/(1024*1024)) "Temporary tablespace SIZE IN MB's" from v$tempfile group by ts#;

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE.

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use DBA_TEMP_FREE_SPACE or V$TEMP_SPACE_HEADER instead.

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMPTBS 4214226944 80740352

select tablespace_name, SUM(bytes_used), sum(bytes_free) from v$temp_space_header where tablespace_name like '%TEMP%' group by tablespace_name;

select TABLESPACE_NAME "TEMP TBS NAME",bytes/(1024*1024) "SIZE(MBs)",BYTES_USED/(1024*1024) "BYTES USED(MBs)",BYTES_FREE/(1024*1024) "BYTES FREE(MBs)" from sys.V_$TEMP_SPACE_HEADER,v$tempfile;

From 11g, we can check free temporary space in new view DBA_TEMP_FREE_SPACE.
SQL> select * from DBA_TEMP_FREE_SPACE;

Resizing tempfile

SQL> alter database tempfile temp-name resize integer K|M|G|T|P|E;

SQL> alter database tempfile '/path/temp01.dbf' resize 1000M;

Resizing temporary tablespace
SQL> alter tablespace temptbs resize 1000M;

Renaming (temporary) tablespace, this is from Oracle 10g

SQL> alter tablespace temp rename to temp2;

In Oracle 11g, temporary tablespace or its tempfiles can be shrunk, up to a specified size.

Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp(orary) files. The optional KEEP clause defines a minimum size for the temporary tablespace or temporary file.

SQL> alter tablespace temp-tbs shrink space;
SQL> alter tablespace temp-tbs shrink space keep n{K|M|G|T|P|E};

SQL> alter tablespace temp-tbs shrink tempfile 'tempfile-name' ;
SQL> alter tablespace temp-tbs shrink tempfile 'tempfile-name' keep n{K|M|G|T|P|E};

The below script reports temporary tablespace usage (script was created for Oracle9i Database). With this script we can monitor the actual space used in a temporary tablespace and see HWM (High Water Mark) of the temporary tablespace. The script is designed to run when there is only one temporary tablespace in the database.

SQL> select sum( u.blocks * blk.block_size)/1024/1024 "MB. in sort segments", (hwm.max * blk.block_size)/1024/1024 "MB. High Water Mark"
from v$sort_usage u, (select block_size from dba_tablespaces where contents = 'TEMPORARY') blk, (select segblk#+blocks max from v$sort_usage where segblk# = (select max(segblk#) from v$sort_usage) ) hwm group by hwm.max * blk.block_size/1024/1024;

alter database tempfile temp-file-name online;
alter database tempfile temp-file-name offline;

alter tablespace temp-ts-name tempfile offline;
alter tablespace temp-ts-name tempfile online;

How to reclaim used space
Several methods existed to reclaim the space used for a larger than normal temporary tablespace.
(1) Restarting the database, if possible.

(2) The method that exists for all releases of Oracle is, simply drop and recreate the temporary tablespace back to its original (or another reasonable) size.
(3) If you are using Oracle9i or higher, drop the large tempfile (which will drop the tempfile from the data dictionary and the OS file system).

From Oracle 11g, while creating global temporary tables, we can specify TEMPORARY tablespaces.

Temporary Tablespace Related Oracle Views:
DBA_TEMP_FILES
DBA_DATA_FILES
DBA_TABLESPACES
DBA_TEMP_FREE_SPACE     (Oracle 11g)

V$TEMPFILE
V$TEMP_SPACE_HEADER
V$TEMPORARY_LOBS
V$TEMPSTAT

V$TEMPSEG_USAGE

Related Oracle Articles: Temporary Tabelspace Groups