Drop and recreate the temporary tablespace 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 tempfile in temporary tablespace and check the space in temporary tablespace.

If size of temp tablespace is full then need to add the more temp file into it.
If you want to clear the old temporary tablespace and recreate new instead of it.

1. Check the default Temporary tablespace set for Oracle Database

set line 200 pages 200
col property_name for a23
col property_value for a14
SELECT property_name,property_value FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE
----------------------- --------------
DEFAULT_TEMP_TABLESPACE TEMP


2. Check the temporary files location

col tablespace_name for a15
col file_name for a50
Select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME FILE_NAME
--------------- ----------------------------------------------
TEMP D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP01.DBF
TEMP D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP02.DBF


3. Create a new Temporary tablespace in database

Create temporary tablespace TMP2 tempfile 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TMP01.DBF' size 500m autoextend on next 100m;


4. Make new temporary tablespace default

alter database default temporary tablespace TMP2;


5. Drop the old temp tablespace including physical files

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;


6. Add more temp files to new TMP2 Tablespace

ALTER TABLESPACE tmp2 ADD TEMPFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\TMP02.DBF' SIZE 100M autoextend on next 100m;

7. Its done

General usage command for Temporary tablespace

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';

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.