This script checks for excessive TEMP tablespace usage and sends an alert with SQL and session details.

#!/bin/bash
#ck_temp_usage.sh: Check all sessions' usage of all temporary tablespace
#To find the sessions that use lots of temp space, query
#select * from gv$tempseg_usage where blocks>128 order by blocks; <-- 128 blocks is very common so you can ignore
#You can find the sessions given the above (select * from gv$session where inst_id=<inst_id> and saddr=<session_addr> and serial#=<session_num>, or the SQLs using temp space (<sql_id_tempseg>), or the space in bytes (<blocks>*8192, but you may want to group by session and/or SQL ID to be meaningful).

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin
ALERT=500 #alert us if total TEMP used exceeds this threshold
RECIPIENT=my_email

cd /u01/app/oracle/scripts/ck_temp_usage #where this script is

#assume db_block_size 8192
RESULT=$(sqlplus -s -L dbsnmp/mypassword@mydb <<EOF
set pages 0 head off feedb off
select round(sum(blocks)*8192/1073741824) gb from gv\$tempseg_usage;
exit
EOF
)

if (( RESULT > ALERT )); then

echo "Total temp space usage $RESULT GB exceeds the alert threshold $ALERT GB. Sessions using most temp space are:" > bigtempsql.log
echo "
INST_ID   SID TEMP_GB SQL_ID        USERNAME        MORE_INFO
------- ----- ------- ------------- --------------- -------------------------------------------------------------------------------" >> bigtempsql.log
sqlplus -s -L dbsnmp/mypassword@mydb <<EOF >> bigtempsql.log
col inst_id for 999999
col sid for 9999
col temp_gb for 999999
col sql_id for a13
col username for a15
col more_info for a80
set pages 0 lin 150 trims on head off feedb off tab off
select a.inst_id, sid, round(blocks*8192/1073741824) temp_gb, sql_id_tempseg sql_id, a.username, module||';'||action||';'||client_info more_info, substr(sql_text,1,150) sql from gv\$session a, gv\$sqlstats b, gv\$tempseg_usage c where a.inst_id=b.inst_id and a.inst_id=c.inst_id and a.saddr=c.session_addr and b.sql_id=c.sql_id_tempseg and blocks>12800 order by 3 desc;
EOF
#The SQL above only shows the sessions using 100+ MB (blocks>12800) temp space. Adjust as needed.

echo "Total temp space usage exceeded: $RESULT GB"
mail -s "MYDB Temp Space Alert" $RECIPIENT < bigtempsql.log

fi

#To test, you may need to create a GTT to use TEMP:
#CREATE GLOBAL TEMPORARY TABLE gtt_temp_test (id NUMBER, data VARCHAR2(1000)) ON COMMIT PRESERVE ROWS;
#INSERT INTO gtt_temp_test SELECT level, rpad('X',1000,'X') FROM dual CONNECT BY level <= 50000;
#The above GTT uses about 57 MB TEMP.