#!/bin/bash
#check_long_run_sql.sh: check long running SQLs and alert if longer than 4 hours (adjust "sysdate-1/6" as needed)
#The query limits to a specific user ("username='SYSADM'" below). Remove the restriction as needed; then add username to the select-list and augment the "echo" line for a header.
#chmod 700 this_script as it has password

RECIPIENT='my_email mycoworker_email'
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db
export PATH=$ORACLE_HOME/bin:/usr/bin:/bin
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'

cd /u01/app/oracle/scripts/ck_longrunsql

echo "INST_ID   SID SQL_EXEC_START    MODULE                                ACTION              CLIENT_INFO                                    SQL_ID
------- ----- ----------------- ------------------------------------- ------------------- ---------------------------------------------- -------------" > longrunsql.log
sqlplus -s -L system/mypassword@mydb <<EOF >> longrunsql.log
col module for a37
col action for a19
col client_info for a46
col inst_id for 999999
col sid for 9999
set pages 0 lin 150 trims on head off feedb off tab off
select a.inst_id, sid, sql_exec_start, a.module, a.action, client_info, a.sql_id, substr(sql_text,1,150) sql from gv\$session a, gv\$sqlstats b where a.inst_id=b.inst_id and a.sql_id=b.sql_id and wait_class!='Idle' and username='SYSADM' and sql_exec_start<sysdate-1/6 order by 3;
EOF
#longrunsql.log has 2 lines for header. If it has more lines, it means the above query has found long running SQLs.
if (( $(wc -l longrunsql.log|awk '{print $1}') >= 3 )); then
  mail -s 'Long running SQLs in the XXX database' $RECIPIENT < longrunsql.log
fi

#Optional:
#The longrunsql.log.hist file records the query result regardless how long the SQLs have been running. Column min is number of minutes they've been running.
date >> longrunsql.log.hist
sqlplus -s -L system/mypassword@mydb <<EOF
col module for a37
col action for a19
col client_info for a46
col inst_id for 999999
col sid for 9999
col min for 99999
set pages 0 lin 160 trims on head off feedb off tab off
spo longrunsql.log.hist append
select a.inst_id, sid, sql_exec_start, (sysdate-sql_exec_start)*1440 min, a.module, a.action, client_info, a.sql_id, substr(sql_text,1,160) sql from gv\$session a, gv\$sqlstats b where a.inst_id=b.inst_id and a.sql_id=b.sql_id and wait_class!='Idle' and username='SYSADM' order by 3;
EOF