Script to Monitor TEMP tablespace usage - Oracle Database Script.
SELECT a.tablespace_name TABLESPACE,
d.TEMP_TOTAL_MB,
SUM (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
d.TEMP_TOTAL_MB - SUM (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
FROM v$sort_segment a, (
SELECT b.name,
c.block_size,
SUM (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
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.TEMP_TOTAL_MB;