Temp Tablespace Utilization

One of the frequent errors in a transactional database is ORA-1652: unable to extend temp segment. On a high level, temp tablespace is used for sorting purpose and it is shared tablespace among different users in the database.

• Check Temp Size

• Temp % Utilization

• Top 10 Sessions Using High Temp

• Find Current Sessions Using Temp

Check Temp Size

This below query will work for Oracle 12c version and above only

SELECT * FROM DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME    TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID
------------------ --------------- --------------- ---------- ------------- ----------
TEMP                     137363456       137363456  134217728 SHARED

Below query will work in any version of Oracle database to check temp tablespace utilization

set lines 200
select TABLESPACE_NAME, sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024) 
from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME;

Temp % Utilization

Query to check percentage (%) utilization of temp tablespace

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

Top 10 Sessions Using High Temp

Finding the top 10 sessions with highest temp tablespace usage

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Find Current Sessions Using Temp

Below is the query to identify the current users who are right now using TEMP tablespace OR utilizing the TEMP segments

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, 
(b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
     (select block_size from dba_tablespaces where tablespace_name='TEMP') d
    WHERE b.tablespace = 'TEMP'
    and a.saddr = b.session_addr
    AND c.address= a.sql_address
    AND c.hash_value = a.sql_hash_value
    AND (b.blocks*d.block_size)/1048576 > 1024
    ORDER BY b.tablespace, 6 desc;

Further Read

• Tablespace Administration