Query To Monitor Temporary Tablespace Usage

Download(temptsmon.sql)

REM +======================================================================+
REM                    
REM File Name: temptsmon.sql
REM 
REM Description:
REM   Query To Check Temporary Tablespace Usage
REM   
REM Notes:
REM   Usage: sqlplus "/ as sysdba" @temptsmon.sql
REM   
REM +======================================================================+

clear columns
set lines 180
col Per_Used for a10
col Per_Free for a10
select TABLESPACE ,round(MB_TOTAL) MB_TOTAL,round(MB_USED) MB_USED,round(MB_FREE) MB_FREE,
                           round(((MB_TOTAL-MB_FREE)*100)/MB_TOTAL)||'%' Per_Used ,
                           round(((MB_TOTAL-MB_USED)*100)/MB_TOTAL)||'%' Per_Free
                from
                (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);