Temporary Tablespace Infomation using Query

1) To know how much space is used in temporary segments:

SQL>Select extent_size*8192*used_extents/1024/1024/1024"space used in GigaByte" from v$sort_segment;

Space used in GigaByte

----------------------

196.5847621

SQl>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_blocks, total_blocks from v$sort_segment;

Tablespace_name Extent_size Total_extents Used_extents Free_extents Max_used_blocks Total_blocks
--------------- ----------- ------------- ------------- ----------- ---------------- ------------
Test 128 11896 4526 7370 1449216 1522688

2) To know SQL ID and Which type of Sort Segment issue:

SQL>select username,sqladdr, sql_id, tablespace, segtype, extents,blocks from v$tempseg_usage;

3) To know which SQL is using Temporary segments query:

SQL>select s.sql_text, t.username, t.tablespace, t.segtype, t.blocs, t.extents

from v$sql s, v$tempseg_usage t

where t.sql_id=s.sql_id;

4) Information about tablespace containing sort segments:

SQL>select tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size from v$sort_segmrnt;

5)The users who is performng sort operation in Temp Segments:

SQL>select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid, a.serial#,a.username, a.osuser, a.status from v$session a,v$sort_usage b
where a.saddr=b.session_addr
order by b.tablespace, b.segfile#, b.segblk#,b.blocks;