top of page
Temp Tablespace Utilization
Monitor and manage temporary tablespace usage in Oracle.
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
bottom of page