Script to find temp tablespace free and used space

tempfree.sql

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;

tempspace.sql

-- GE Restricted

select v.sid, v.serial#, v.status, v.username, s.tablespace, s.segtype

from v$session v, v$sort_usage s

where v.saddr=s.session_addr

/

Script Name : temp_top_sql.sql

description : Run below script to monitor the temp tablespace usage and check what running sqltext.

set linesize 400

column osuser format a20

column sid format 9999

column serial# format 99999

column username format a20

column program format a20

column vp.value format 99999999

        select s.osuser, s.sid, s.serial#,s.username, s.program,

               sum(u.blocks)*vp.value/1024/1024 sort_size_MB

        from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

        where  s.saddr = u.session_addr

          and  vp.name = 'db_block_size'

            group  by s.osuser, s.sid, s.serial#,s.username, s.program, vp.value;

SELECT s.sid, s.status, sq.address,  sq.sql_id, sq.sql_text

FROM v$session s, v$sql sq

WHERE s.status = 'ACTIVE'

and s.sql_hash_value = sq.hash_value

and s.sql_address = sq.address

and s.sid IN (

select s.sid

     from   sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp

where  s.saddr = u.session_addr

            and  vp.name = 'db_block_size'

         group  by s.osuser, s.sid, s.serial#,s.username, s.program, vp.value);