TEMP space usage overall and by session

Get current total TEMP tablespace currently allocated, used and free:

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;

TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP2 27000 80 26920

Another version of the script:
SELECT a.tablespace_name
,to_char(a.total/(1024*1024), 'FM999,990.00') total_MB
,to_char(a.used/(1024*1024), 'FM999,990.00') used_MB
,to_char((a.total - NVL(a.used, 0))/(1024*1024), 'FM999,990.00') free_MB
FROM (
SELECT tablespace_name
,block_size
,(SELECT SUM(v$sort_usage.blocks * block_size)
FROM v$sort_usage
WHERE v$sort_usage.TABLESPACE = dba_tablespaces.tablespace_name) used
,(SELECT SUM(bytes) FROM dba_temp_files WHERE tablespace_name = dba_tablespaces.tablespace_name) total
FROM dba_tablespaces
WHERE contents = 'TEMPORARY'
) a
/

TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB
—————————— ———– ———– ———–
TEMP2 100.00 100.00

1 row selected.

Get Current TEMP space usage by session id (9i/10g):

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
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,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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