Wednesday, May 22, 2019

Oracle 12c -- How to Find Max Space Used by a TEMP Tablespace


You want to identify how much TEMP space was allocated max


Oracle Database 12c provides an AWR view called DBA_HIST_TBSPC_SPACE_USAGE. This view display historical TEMP tablespace usage statistics. 

First, identify the ID and block size of the TEMP tablespace you are interested in (as you may have several TEMP tablespaces):

select  tsname, ts#, block_size 
where contents = 'TEMPORARY'

--------- ---------- ----------
TEMP               3       8192

Having identified the ID of you TEMP tablespace, pass it to DBA_HIST_TBSPC_SPACE_USAGE:

select max(tablespace_usedsize)*8192/1024/1024/1024 as size_GB
from dba_hist_tbspc_space_usage 
where tablespace_id = 3

The output will be the maximum TEMP space used within AWR retention target.

To check your AWR retention use:

select retention from dba_hist_wr_control

You may also want to find out which sessions were the top TEMP space consumers:

select instance_number, session_id, session_serial#, max(temp_space_allocated)/1024/1024/1024 
from dba_hist_active_sess_history
group by instance_number, session_id, session_serial#
order by 4 desc nulls last;

No comments: