Temp space check in oracle

Ever got this issue in oracle ... ORA-01652: unable to extend temp segment by x in tablespace TEMP_TS Here is a way to find out if you are really out of temp space
--Temp space total size, free space and used space
SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024/1024 AS Total_GB,
used_blocks*16/1024/1024 AS Used_GB,
free_blocks*16/1024/1024 AS Free_GB
FROM   v$sort_segment;
 
--Temp space utilization by user
 
SELECT
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
b.username,
b.blocks*16/1024/1024 AS GB,
a.SID,
a.serial#,
a.status
FROM v$session a, v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.username, b.tablespace, b.blocks;
FacebookTwitterGoogle+Share