How to Monitor Temp Tablespace usage and Sort Operations

Use following Script to monitor Temp tablespace and Sort Operations in Oracle Database.

------------Start of Script-------------------------------------


#!/bin/sh
# Temp Tablespace & Sort segments Usage
#

SPOOL_FILE=temp_seg.html
FINAL_FILE=Temp_Segments.html
echo "Content-Type: text/html" >> ${FINAL_FILE}
sqlplus -s "/ as sysdba" EOF
set echo off
set pagesize 220
set markup html on spool on
spool ${SPOOL_FILE}
TTITLE CENTER 'Temp Tablespace and Sort Segments Usage'


SELECT gvs.inst_id "Instance",
gvs.SID,
gvs.username "User Name",
NVL (gvs.status, ' ') "Status",
NVL (gvs.osuser, ' ') "OS User",
NVL (gvs.program, ' ') "Program",
NVL (gvs.module, ' ') "Module",
gsu.TABLESPACE "Tablespace Name",
SUM (gsu.extents) "# of Ext",
SUM (gsu.blocks) "# of Blocks"
FROM gv$session gvs,
gv$sort_usage gsu
WHERE gvs.saddr = gsu.session_addr
AND gvs.inst_id = gsu.inst_id
GROUP BY gvs.inst_id,
gvs.SID,
gvs.username,
gvs.status,
gvs.osuser,
gvs.program,
gvs.module,
gsu.TABLESPACE
ORDER BY 9 DESC;

spool off

set markup html off spool off
EOF

if [ `grep -c 'no rows selected' ${SPOOL_FILE}` -eq 1 ]
then
rm ${FINAL_FILE}
rm ${SPOOL_FILE}
exit 0
fi


cat ${SPOOL_FILE} | grep -v 'rows selected' >> ${FINAL_FILE}


(echo "Importance: High"; echo "Subject: $ORACLE_SID: Temp Tablespace & Segment Usage"; cat ${FINAL_FILE}) | /usr/sbin/sendmail -F $ORACLE_SID test@testorcl.com


rm ${FINAL_FILE}
rm ${SPOOL_FILE}


------------End of Script--------------------------------------