How to monitor temporary tablespace usage in Oracle?

Problem:

You want to monitor the usage of the temporary tablespace in Oracle.

Solution:

We can use the following query to find out the used and free space in a temporary tablespace.

We will begin with identifying temporary table space names.

Example

<p>SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';</p>

Output

<p>TEMP</p>

Next up we will use below SQL to identify the used and free space in temporary tablespace.

Example

<p>SELECT *
FROM
  (SELECT a.tablespace_name,
    SUM(a.bytes/1024/1024) allocated_mb
  FROM dba_temp_files a
  WHERE a.tablespace_name = 'TEMP'
  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 = 'TEMP'
  GROUP BY b.tablespace_name
  );</p>

Output

<p>TEMP    4600    4568    32</p>

We will identify the user and the SQL statements responsible for the high temporary tablespace usage.

Example

<p>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;</p>

We can use the following query to find out which sessions are using space in the temporary tablespace.

Example

<p>SELECT s.sid
  || ','
  || s.serial# sid_serial,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  SUM (o.blocks) * t.block_size / 1024 / 1024 mb_used,
  o.tablespace,
  COUNT(*) sorts
FROM v$sort_usage o,
  v$session s,
  dba_tablespaces t,
  v$process p
WHERE o.session_addr = s.saddr
AND s.paddr          = p.addr
AND o.tablespace     = t.tablespace_name
GROUP BY s.sid,
  s.serial#,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  t.block_size,
  o.tablespace
ORDER BY sid_serial;</p>