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;
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;