1. Home
  2. Knowledge Base
  3. Scripts
  4. Monitoring
  5. Script – Temporary tablespace usage
  1. Home
  2. Knowledge Base
  3. Scripts
  4. Script – Temporary tablespace usage

Script – Temporary tablespace usage

Listing of temp segments.

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Temp segment usage per session.

SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

The following two tabs change content below.

Arjun Raja

Latest posts by Arjun Raja (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles


  1. Hi Gavin,

    Thanks for wonderful sql statements to find temp segment usage per session.

    I would like to draw your attention for firet sql statement.When i run first sql statement in my testing enviroment. It was throwing following error message ORA-00911 “Invalid Character”.I removed minus sign and put it again.After this it worked fine.

    I was able to find culprit session with use of second sql statement.

    Good work keep it up.


Leave a Comment