TEMP Tablespace size checks

# Run the following statement to check the free space within the used portion of TEMPFILE #

//

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;

//

#  The query below will display which sessions are using TEMP tablespace and how much space is being used by each session: #


//

set line 300 pagesize 300
col TABLESPACE for a10
col OSUSER for a10
col STATUS for a15
col PROGRAM for a30
col USERNAME for a30
SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) used_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE, b.segfile#, b.segblk#, b.blocks;


//


# Temp usage #real time
======================
//

col USERNAME for a20
col MODULE for a30
col SPID for a10
SELECT S.sid , S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

//

TEMP_Usage_history
==================

select  thedate,
 gbsize,
 prev_gbsize,
 gbsize-prev_gbsize diff
from        (
 select  thedate,
 gbsize,
 lag(gbsize,1) over (order by r) prev_gbsize
 from    (
 select  rownum r,
 thedate,
 gbsize
 from    (
 select  trunc(thedate) thedate,
 max(gbsize) gbsize
 from    (
 select  to_date(to_char(snapshot.begin_interval_time,'YYYY-MON-DD HH24:MI:SS'),'YYYY-MON-DD HH24:MI:SS') thedate,
 round((usage.tablespace_usedsize*block_size.value)/1024/1024/1024,2) gbsize
 from    dba_hist_tbspc_space_usage usage,
 v$tablespace               tablespace,
 dba_hist_snapshot          snapshot,
 v$parameter                block_size
 where   usage.snap_id       = snapshot.snap_id
 and     usage.tablespace_id = tablespace.ts#
 and     tablespace.name     = '&tablespace'
 and     block_size.name     = 'db_block_size'
 )
 group by
 trunc(thedate)
 order by
 trunc(thedate)
 )
 )
 );

//


SELECT   S.sid || ',' || S.serial# sid_serial, S.username,T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
T.sqladdr address, Q.hash_value, Q.sql_text
FROM     v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE    T.session_addr = S.saddr
AND      T.sqladdr = Q.address
AND      T.tablespace = TBS.tablespace_name
and s.process='23426'
and s.status = 'ACTIVE'
ORDER BY S.sid;

Resizing temp:
============

 select FILE_NAME,bytes/1024/1024 from dba_temp_files where TABLESPACE_NAME='TEMP';     // checking temp file//

alter database tempfile '+DATA/orcl/tempfile/temp.275.844266939' resize 1G;             // Resizing Temp file //