Temporary Tablespace

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.

Recreate Temporary Tablespace:

  1. Login to oracle database as # sqlplus / as sysdba
  2. Find the temporary tablespace path by using below command

        SQL > select * from v$tempfile;

 3. Create new temporary table space by passing the path and proper file name.
      SQL > create temporary tablespace TEMP2 tempfile '/home/oracle/app/oracle/oradata/db11gr2/temp02.dbf' size 100M reuse autoextend on;

 4. Now make it as default temporary tablespace.
      SQL > alter database default temporary tablespace TEMP2;
  
 5. Now restart DB

 6. Drop old temporary tablespace by passing the path and proper file name.

SQL > alter database tempfile '/home/oracle/app/oracle/oradata/db11gr2/temp01.dbf' drop including datafiles;

Check Free Space,Used Space,Total Space,Instance Name and Host Name by running below query.

select tablespace_name,(free_blocks*8)/1024/1024 "Free Space in GB",(used_blocks*8)/1024/1024 "Used Space in GB",
(total_blocks*8)/1024/1024 "Total Space GB",
t.instance_name,
t.host_name 
from gv$sort_segment ss,gv$instance t 
where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and t.inst_id=ss.inst_id;

OR run the below query

select (select sum(Bytes)/1024/1024/1024 from dba_temp_files) "TOTAL SPACE in GB",(select decode(sum(blocks),null,0,sum(blocks)*8192/1024/1024/1024) from v$sort_usage) "CURRENT USAGE in GB",
(select count(*) from v$sort_usage where blocks>(100000000/8192))"SESSIONS USING > 1000MB" 
from dual;