TEMP USAGE

Users that consumes TEMP:

REM Description: Lists the details of TEMP tablespace and the users that consumes TEMP
REM Usage: tmptb.sql
REM +++++++++++++++++++++++++++
SELECT tablespace_name, total_blocks, used_blocks, free_blocks, total_blocks*16/1024 as total_MB, used_blocks*16/1024 as used_MB, free_blocks*16/1024 as free_MB FROM v$sort_segment;

SELECT   b.TABLESPACE,b.segfile#,b.segblk#,b.blocks,b.blocks*16/1024 as MB,a.SID,a.serial#,a.status FROM v$session a, v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.TABLESPACE,b.segfile#,b.segblk#,b.blocks;

======================================================================

Temp Usage:

set pages 20000 lines 120 feed off verify off

prompt

prompt Tempfile Details :

col tsname for a20

col fname for a80

select a.name Tsname,b.name Fname,b.bytes/1024/1024 size_mb

from v$tablespace a,v$tempfile b

where a.ts#=b.ts#;

prompt

prompt Temporary space usage :

select  sysdate dtstamp,

         s.tablespace_name,

         d.tbspc_mb,

         s.total_blocks*(select value from v$parameter where name='db_block_size')/1024/1024 temp_tot_mb,

         s.used_blocks*(select value from v$parameter where name='db_block_size')/1024/1024 temp_used_mb,

         s.free_blocks*(select value from v$parameter where name='db_block_size')/1024/1024 temp_free_mb

from    v$sort_segment s,

         (select tablespace_name,sum(bytes/1024/1024) tbspc_mb

         from dba_data_files

         group by tablespace_name

         union

         select tablespace_name,sum(bytes/1024/1024) tbspc_mb

         from dba_temp_files

         group by tablespace_name) d

where s.tablespace_name=d.tablespace_name;

prompt

prompt Temprary space consumers :

select s.username, s.sid, u.tablespace, u.contents, 

round(u.blocks*(select value from v$parameter where name='db_block_size')/1024/1024,2) MB 

from v$session s, sys.v$sort_usage u 

where s.saddr = u.session_addr 

and u.contents = 'TEMPORARY' 

order by MB desc; 

clear columns

clear breaks

=================================================================================

TEMP SIZE:

set lines 200 pages 999

col file_name format a60

col tablespace_name format a40

select TABLESPACE_NAME, sum(BYTES_USED)/1024/1024 used_mb, sum(BYTES_FREE)/1024/1024 free_mb, round(sum(BYTES_FREE)/(sum(BYTES_FREE)+sum(BYTES_USED)) * 100,2) pct_free from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME;

select file_name, tablespace_name, bytes/1024/1024 

  from dba_temp_files;

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, 

a.username, a.osuser, a.status 

FROM v$session a,v$sort_usage b 

WHERE a.saddr = b.session_addr 

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;