Temp Usage
----------
select TABLESPACE_NAME,total_blocks*to_number(value)/1024/1024 total_MB
,used_blocks*to_number(value)/1024/1024 used_MB
,free_blocks*to_number(value)/1024/1024 free_MB
,max_used_blocks*to_number(value)/1024/1024 max_used_MB
,max_blocks*to_number(value)/1024/1024 max_MB
,max_sort_blocks*to_number(value)/1024/1024 max_Indv_sort_MB
from v$sort_segment,v$parameter where name='db_block_size';
----------------------------------------------------------
v$temp_space_header instead of dba_free_space;
SELECT tablespace_name, SUM(bytes_used)/1024/1024 AS bytes_used_mb,
SUM(bytes_free)/1024/1024 as bytes_free_mb FROM V$temp_space_header GROUP BY
tablespace_name;
select * from v$temp_space_header;
-------------------------------------------------------------
To report true free space within the used portion of the TEMPFILE:
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;
How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.
If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.saddr = u.session_addr
/
select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value
/
------------------------------------------------------------------------------------------
select CURRENT_USERS,TOTAL_BLOCKS,EXTENT_SIZE,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS,
MAX_USED_SIZE,MAX_USED_BLOCKS from v$sort_segment;
_-------------------------------------------------------------------------------------
First, you need to find out if your applications need all the space which they are using for the temp, and tune as appropriate.
Second, you may have falled into the trap of using OEM or some other such tool which does not show the 'true' space available in the temp tablespace. Such tools only show the maximum space ever allocated, not the current space allocated (or rather segments). A tool such as DB Console or Grid will show at any point in time the 'true' or current segments allocated. You can also use the scripts below:
SELECT SUM (u.blocks * blk.block_size) / 1024 / 1024 "Mb. in sort segments"
, (hwm.MAX * blk.block_size) / 1024 / 1024 "Mb. High Water Mark"
FROM v$sort_usage u
, (SELECT block_size
FROM DBA_TABLESPACES
WHERE CONTENTS = 'TEMPORARY') blk
, (SELECT segblk# + blocks MAX
FROM v$sort_usage
WHERE segblk# = (SELECT MAX (segblk#)
FROM v$sort_usage)) hwm
GROUP BY hwm.MAX * blk.block_size / 1024 / 1024;
Temp_Status
-----------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN tablespace_name FORMAT a18 HEAD 'Tablespace Name'
COLUMN tablespace_status FORMAT a9 HEAD 'Status'
COLUMN tablespace_size FORMAT 999,999,999,999 HEAD 'Size'
COLUMN used FORMAT 999,999,999,999 HEAD 'Used'
COLUMN used_pct FORMAT 999 HEAD 'Pct. Used'
COLUMN current_users FORMAT 9,999 HEAD 'Current Users'
SELECT
d.tablespace_name tablespace_name
, d.status tablespace_status
, NVL(a.bytes, 0) tablespace_size
, NVL(t.bytes, 0) used
, TRUNC(NVL(t.bytes / a.bytes * 100, 0)) used_pct
, NVL(s.current_users, 0) current_users
FROM
sys.dba_tablespaces d
, ( select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name
) a
, ( select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name
) t
, v$sort_segment s
WHERE
d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
/
