Temp Tablespace Usage/Free

Temp Tablespace Usage/Free

TEMP Usage per session ?

SQL> SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

         COUNT(*) sort_ops

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,

         S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

Temp Space used/free?

SQL> select tablespace_name, bytes_used/1024/1024, bytes_free/1024/1024 from v$temp_space_header;

Users Using Temp tablespace?

SQL> select b.tablespace, (b.blocks*p.value)/1024/1024 , a.sid, a.username from sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p

where p.name=b.session_addr;

To get which user is using temp tablespace ?

SQL> select username,tablespace,extents from v$sort_usage;

Free and Used Space in Temp Tablespace ?

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;

Temp Usage By statement ?

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

ORDER BY S.sid;

User quotas on all tablespaces :

SQL> col quota format a10

select username

,      tablespace_name

,      decode(max_bytes, -1, 'unlimited'

       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"

from   dba_ts_quotas

where  tablespace_name not in ('TEMP');

Temp Usage:

SQL> set pagesize 10000;

           set linesize 10000;

select

   srt.tablespace,

   srt.segfile#,

   srt.segblk#,

   srt.blocks,

   a.sid,

   a.serial#,

   a.username,

   a.osuser,

   a.status

from

      v$session    a,

   v$sort_usage srt

where

   a.saddr = srt.session_addr

order by

   srt.tablespace, srt.segfile#, srt.segblk#,

   srt.blocks;

SQL> select sum(free_blocks) from gv$sort_segment  where tablespace_name ='TEMP';

SQL> select file_id, tablespace_name, bytes  from dba_temp_files;

SQL> select inst_id,tablespace_name,total_blocks,used_blocks,free_blocks from gv$sort_segment;

SQL>  select username, tablespace, blocks from v$sort_usage;

SQL>select tablespace_name, total_blocks,used_blocks, free_blocks  from v$sort_segment

 SQL> select tablespace_name, file_id, bytes_used, bytes_free  from v$temp_space_header ;

SQL> select tablespace_name,extent_size,total_extents,total_blocks,max_size,max_blocks from v$sort_segment;

SQL>  select tablespace_name, file_id, bytes_used, bytes_free  from v$temp_space_header;

++++++++++++++++++++