to check temp tablespace growth and monitor

 

Useful queries to find temp tablespace usage and monitor:

SELECT * FROM dba_temp_free_space;

--------------------------------------------------------------------------------------------------------------------------------

select a.tablespace_name tablespace,d.TEMP_TOTAL_MB,

sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,

d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB

from v$sort_segment a,

(

select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB

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.TEMP_TOTAL_MB;

Output:

--------------------------------------------------------------------------------------------------------------------------------

select sum(maxbytes/1024/1024/1024) from dba_temp_files where tablespace_name='PSTEMP'

--------------------------------------------------------------------------------------------------------------------------------

set pages 999

set lines 400

col FILE_NAME format a75

select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB

from dba_temp_files d,

 v$tempfile v

where d.FILE_ID = v.FILE#

order by d.TABLESPACE_NAME, d.FILE_NAME;

--------------------------------------------------------------------------------------------------------------------------------