Thinking Out Loud

October 16, 2011

Shell and SQL Script

Filed under: oracle,shell scripting — mdinh @ 10:36 pm

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;
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: