Manage the temporary table space in Oracle

Temporary tablespace is used in Oracle by sql query or long jobs, like sorting operation which is not fit in the physical memory of the machine. Following steps let us help to configure the temp tablespace as default, how to create the temporary tablespace, how to add datafile in temporary tablespace and check the space in temporary tablespace.

Check the temporary files and tablespace

Select * from dba_temp_files;


Create new Temporary tablespace in database

Create temporary tablespace TEMP2 tempfile '/u02/oradata/temp_01.dbf'  size 500m;


Drop the temp tablespace including physical files:

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;


Add temp files to TEMP Tablespace

ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/temp_03.dbf' SIZE 100M;


Check the usage of Temporary tablespace

select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks,
a.sid, a.serial#, a.username, a.osuser, a.status
from v$session    a, v$sort_usage srt
where a.saddr = srt.session_addr
order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks;


Check and Set the Default Temporary Tablespace

SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

 
Modify the temp tablespace as default

alter database default temporary tablespace temp;


Checked Free space in Temporary tablespace

Select tablespace_name , Bytes_used, Bytes_free from v$TEMP_SPACE_HEADER;


Note:
1. One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database.
2 . If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty.