Temporary Tablespaces in Oracle Database

What are Temporary Tablespace

Temporary Tablespace contains transient data that persists only for the duration of the session.

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation.

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:- A temporary tablespace does not contain permanent objects and therefore doesn't need to be backed up.

How to create a temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

Tablespace created.

Default Temporary Tablespace

Default Temporary tablespace can be defined at the database creation time or by issuing an "ALTER DATABASE"

SQL> alter database default temporary tablespace temp1;

Database altered.


Restrictions :-
1. Default Temporary tablespace cannot be dropped till you create another one.
2. Default Temporary tablespace cannot be taken off-line.

If you define a default temporary tablespace , it's automatically assigned to users.

How to find default temporary tablespace of database


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE          DESCRIPTION
------------------------------                           ------------------------------           ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                               Name of default temporary tablespace

How to change default Temporary Tablespace

1. create a new temporary tablespace

SQL> create temporary tablespace temp1 tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP01.dbf' size 100m;

2. Assign new temporary tablespace as the default temporary tablespace.


SQL> alter database default temporary tablespace temp1;

Database altered.

3. Drop the OLD default temporary tablespace


SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

4. Check that default temporary tablespace changed or not


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE         DESCRIPTION
------------------------------                           ------------------------------         ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP1                           Name of default temporary tablespace

What are Temp Files

Details of Temp files in the database are not recorded in the control file, means we can recreate them whenever we restore the database or after deleting them accidently.

We cannot remove datafiles from a tablespace till we won't drop the entire tablespace. But we can remove tempfiles from a database :-

How to remove tempfiles from a database :-


SQL> alter database tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' drop including datafiles;

Database altered.

How to add tempfiles to a database:-

SQL> alter tablespace temp add tempfile 'H:\APP\NEWADMIN\ORADATA\ORCL\TEMP02.DBF' size 100m;

Getting Usage Details of Temp tablespace in a database :-

##################### RAC Database ###############################

SQL> Select INST_ID,TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from gv$temp_space_header group by INST_ID,TABLESPACE_NAME;

##################### StandAlone Database ###############################

SQL> Select TABLESPACE_NAME,sum(BYTES_USED/1024/1024/1024),sum(BYTES_FREE/1024/1024/1024) from v$temp_space_header group by TABLESPACE_NAME;

######################################################################

SQL> Select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                TABLESPACE_SIZE     ALLOCATED_SPACE      FREE_SPACE
------------------------------                   ---------------                     ---------------                     ----------
TEMP                                       104857600                     8388608                          103809024

########################## INSTANCE WISE ##############################


 SQL> select tablespace_name,
 (free_blocks*8)/1024/1024 FreeSpaceGB,
 (used_blocks*8)/1024/1024 UsedSpaceGB,
 (total_blocks*8)/1024/1024 TotalSpaceGB,
 i.instance_name,i.host_name
 from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
 i.inst_id=ss.inst_id;


TABLESPACE_NAME          FREESPACEGB     USEDSPACEGB    TOTALSPACEGB     INSTANCE_NAME    HOST_NAME
------------------------------            -----------                   -----------                  ------------                    ----------------               ------------------------------
TEMP                                 .007                       .000                      .007                           orcl                          AMIT-PC

I hope this article hepled you.

Regards,
Amit Rath