A quick note on separating SQL from shell script.
If I have a shell script name longops.sh, then it will call longops.sql.
Don’t embed SQL in shell script so that SQL script can be executed independently.
> cat longops.sh
#!/bin/sh
. /home/oracle/.common.conf > /dev/null
DN=`dirname $0`
BN=`basename $0`
sql=`echo $BN|cut -d'.' -f1`.sql
for sid in `ps -eo args|grep ora_smon|grep -v grep|awk -F_ '{print $3}'`
do
OUTF=/tmp/longops.${sid}.html
rm -f $OUTF
ORACLE_SID=$sid
. oraenv
$ORACLE_HOME/bin/sqlplus -SL > /dev/null / << END
spool $OUTF
prompt From: oracle@mail.com
prompt To: dba@mail.com
prompt Subject: WARNING ${BN}: $ORACLE_SID - SQL running over one hour
prompt Content-type: text/html
prompt MIME-Version: 1.0
set markup html on entmap off table 'BORDER="2"'
@${sql}
spool off
exit
END
if [ "$?" -ne "0" ]; then
mailx -s "FAILED: $BN $*" $EMAIL < /dev/null
exit 1
fi
if [ `grep -ic MACHINE $OUTF` != 0 ]; then
/usr/sbin/sendmail -t < $OUTF
fi
done
exit
> cat longops.sql
ALTER SESSION SET NLS_DATE_FORMAT='Dy, DD-Mon-YYYY HH24:MI:SS';
SELECT s.sid, s.serial#,
s.username, s.osuser, s.machine, s.program,
s.sql_id, s.sql_exec_start, s.logon_time,
ROUND (s.last_call_et/3600,3) last_call_hr,
ROUND ((SYSDATE-s.SQL_EXEC_START)*24,3) exec_start_hr,
CASE WHEN s.state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END AS state,
CASE WHEN s.state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END AS sw_event
FROM v$session s
WHERE s.type = 'USER' AND s.status = 'ACTIVE' AND ROUND (s.last_call_et/3600)>1
ORDER BY s.last_call_et DESC;
Advertisement
