Temporary Tablespace in Oracle
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
e.g.
SQL> CREATE DATABASE oracular .....
DEFAULT TEMPORARY TABLESPACE temp_ts .....;
Tempfiles (Temporary Datafiles)
Unlike normal datafiles, tempfiles 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 temp 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 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;
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).
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
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 temp tablespace
SQL> ALTER USER user1 SET TEMPORARY TABLESPACE temp_tbs;
Assigning temporary tablespace group as default temporary tablespace:
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.
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
SQL> select file#, name, round(bytes/(1024*1024),2) "SIZE IN MB's" from v$tempfile;
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
From 11g, we can check free temp 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;
Shrinking
In Oracle 11g, temporary tablespace or it's tempfiles can be shrinked, up to specified size.
Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp files. The optional KEEP clause defines a minimum size for the tablespace or temp 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;
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 11g, while creating global temporary tables, we can specify TEMPORARY tablespaces.
Related 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
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_grp;
