• Arun Kumar

Temp Tablespace utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp tablespace is a shared tablespace among all the users inside the database. Proper space utilization is must in order to avoid ORA-1652 error.


ORA-1652


ORA-1652: unable to extend temp segment by 1024 in tablespace TEMP


Checking 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 percentage (%) 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;



189 views

Recent Posts

See All

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat

Check Oracle database size

There are several ways to measure the size of an Oracle database. As an Oracle DBA you may face the requirement to get the current database size. Find below the queries which you can use to find the s

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback