What is causing my ORA-1652?

ORA-1652: unable to extend temp segment by … in tablespace …

Can be caused by a single TEMP-intensive query, and also by a parallel query whose parallel query slaves are consuming too much TEMP in total.

To find the alert timestamps:

select 
originating_timestamp, module_id, message_text
from v$diag_alert_ext 
where message_text like '%ORA%1652%'
order by originating_timestamp desc
;

To find the top TEMP consumers in a given time frame:

select 
sample_time, module, action, qc_session_id, sum(temp_space_allocated/1024/1024/1024) temp_gb, count(distinct(session_id)) parallel_sessions
from SYS.DBA_HIST_ACTIVE_SESS_HISTORY
where sample_time between '&&t1' and '&&t2'
group by sample_time, module, action, qc_session_id
order by 5 desc
;

Now combine the two queries, and look for possible causes for the ORA-1652 in the top TEMP consumers around the time of the incident.

We assume that the TEMP overallocation happens right before the ORA-1652 incident, so that sample_time (in which we are interested) is always earlier than the originating_timestamp from the alert log.

Also, we assume to see 10 second intervals in the historical ASH, so that the 10 second INTERVAL expression can be used to join the v$diag_alert_ext to the ASH. Practically speaking: the overallocation happens somewhere around 10 seconds before the ORA-1652 event.

The final query:

select 
sample_time, originating_timestamp, module, action, qc_session_id, sum(temp_space_allocated/1024/1024/1024) temp_gb, count(distinct(session_id)) parallel_sessions
from SYS.DBA_HIST_ACTIVE_SESS_HISTORY ash, 
(
select 
originating_timestamp
from v$diag_alert_ext 
where message_text like '%ORA%1652%'
) ora1652_alerts
where 
ash.sample_time < ora1652_alerts.originating_timestamp 
and ora1652_alerts.originating_timestamp - ash.sample_time < interval '10' second 
and temp_space_allocated > 0
group by sample_time, originating_timestamp, module, action, qc_session_id
order by 6 desc
;