Temp tablespace

The following Queries may help you, how much TEM tablespace is used and how much free.

SELECT tablespace_name, extent_size, total_extents, used_extents,

free_extents, max_used_size

FROM v$sort_segment;

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE 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;

####Who is using the temp tablespace:####

SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks

FROM v$session s, v$sort_usage u

WHERE s.saddr=u.session_addr;

select username,extents from v$tempseg_usage;