top of page

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


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


Related Posts

Heading 2

Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

bottom of page