As a DBA you may face a problem with your database if TEMP tablespace gets full. Usually, TEMP table space is used for “sorting and aggregate operations“.

One of the reasons your TEMP tablspace get full and expanding, is that the application queries are not well optimized/tuned.

the following queries will eventually help you in diagnosis:

To Find SQL Statements that is causing TEMP SPACE TO BE FILLED:
//——————————————————————–

select s.sid || ‘,’ || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name
order by s.sid;
/ /———————————————————————
To Find Current TEMP SPACE SIZE:

select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper(‘&&temp_tsname’) group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper(‘&&temp_tsname’) group by b.tablespace_name);