Oracle Temporary Table Space size

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);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s