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

Problem

You want to identify how much TEMP space was allocated max

Solution

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 

from DBA_HIST_TABLESPACE 

where contents = 'TEMPORARY'

TSNAME           TS# BLOCK_SIZE

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

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;