Temp TableSpace

Temp TableSpace Usage

Queries to check usage

 -- Oracle Query-- 

SELECT m.tablespace_name, m.used_percent, (m.tablespace_size - m.used_space)*t.block_size/1024/1024 mb_free 

FROM dba_tablespace_usage_metrics m, dba_tablespaces t, v$parameter p 

WHERE p.name = 'statistics_level' and p.value != 'BASIC' 

AND t.contents = 'TEMPORARY' 

AND t.tablespace_name = m.tablespace_name 

select tablespace_name,sum(bytes_used)/1024/1024/1024,sum(bytes_free)/1024/1024/1024 from gv$temp_space_header group by tablespace_name;

select b.Total_MB, b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,
round(used_blocks*8/1024)   Current_Used_MB,
round(max_used_blocks*8/1024)  Max_used_MB
from v$sort_segment a,
 (select round(sum(bytes)/1024/1024) Total_MB from dba_temp_files ) b;

TEMP tbs usage 

SQL_ID that used max temp tbs within last 1 day ( >5 gigs)

select SESSION_ID,SESSION_SERIAL#,USER_ID,sql_id,TEMP_SPACE_ALLOCATED/(1024*1024*1024)

from DBA_HIST_ACTIVE_SESS_HISTORY 

where  sample_time > sysdate-1 and 

TEMP_SPACE_ALLOCATED > (5*1024*1024*1024)

Tablespace Management .

select FILE_NAME,file_id,TABLESPACE_NAME,BYTES/1024/1024/1024,MAXBYTES/1024/1024 from dba_temp_files where TABLESPACE_NAME='TEMP';

alter database tempfile 5 resize 16G;

ALTER TABLESPACE TEMP SHRINK SPACE KEEP 50G;     -- It will shrink and resize temp tablespace to 50GB and can be used in lower instance when no sorting happening .

sort area being used by temp tablespace-----

SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;