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 ;