Oracle下创建cronjob:检测sql语序并发邮件

20210217：创建一个cronjob。可以检测是否有指定sql query运行超过2分钟，检测到后自动发送邮件

Create a cronjob monitor sql runnings for more than 2 mins. Capture sql_id and sql_text.

指定sql query 内容：

SELECT u%DOCVERSION%C%

#!/bin/bash #所有的cronjob 都要以这个开头

MAILFILE=/tmp/mon2mins.txt #要发送的文件内容

MAIL_RECIPIENT=”xxxxxxx@qq.com” #收件人

######################

rm -f $(MAILFILE) #先将MAILFILE 删了

export ORACLE_SID=****

export ORACLE_BASE=/u01/app/oracle/prodcut.11.2.0.4/dbhome_1

export ORACLE_HOME=/u01/app/oracle/prodcut.11.2.0.4/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$export LD_LIBRARY_PATH

#设置环境变量

Sqlplus -s /nolog <<EOF #sqlplus 登录 然后再开始位置加EOF

conn /as sysdba

SPOOL $(MAILFILE) #spool可以将接下来的sql result输入到指定file：mailfile

Set feedback off #不想显示多少结果，所以把feeback set off，这样下面if [ $RES -gt 0 ]

判断会用到，如果没结果，feedback是0，就不需要发送结果了

Set serveroutput on #将dmbs_output 结果输出

DECLARE

CURSOR find_sql_text_table # 创造一个work area，并将接下来select出来的数据放进去，为了以后运用

IS

WITH x AS (SELECT

St.sql_id SQL_ID,

St.sql_text, SQL_TEXT,

(SYSDATE - ss.sql_exec_start) * 24*60*60 AS DURING

From v$session ss JOIN v$sqltext st ON (st.sql_id == ss.sql_id) where st.sql_text like ‘SELECT u%DOCVERSION%C%’)

Select sql_id, sql_text, during FROM x WHERE during >= 120;

V_sqltext CLOB; # clob:数据库中的一种保存文件所使用的类型,可以保存大段文字 或 long类型：长正数类型

BEGIN

DBMS_OUTPUT.DISABLE;

DBMS_OUTPUT.ENABLE(100000);

FOR cur_rec IN find_sql_text_table

LOOP

BEGIN

SELECT sql_fulltext INTO v_sqltext FROM v\$sql WHERE sql_id=cur_rec.sql_id;

DMBS_OUTPUT.PUT_LINE(‘SQL ID: ‘ || cur_rec.SQL_ID);

DMBS_OUTPUT.PUT_LINE(‘SQL TEXT: ‘ || v_sqltext);

DMBS_OUTPUT.PUT_LINE(‘DURING: ‘ || cur_rec.DURING || ‘ seconds’);

DMBS_OUTPUT.PUT_LINE(‘.......................’);

END;

END LOOP;

END;

/SPOOL OFF

EOF

#############

如果v_sqltext 设置为long类型，则：

BEGIN

DBMS_OUTPUT.DISABLE;

DBMS_OUTPUT.ENABLE(100000);

FOR cur_rec IN find_sql_text_table

LOOP

BEGIN

FOR x IN (SELECT sql_text FROM v\$sqltext WHERE sql_id=cur_rec.sql_id ORDER BY piece)

LOOP

v_sqltext:=v_sqltext || x.sql_text;

EXIT WHEN length(v_sqltext) > 4000;

END LOOP;

DMBS_OUTPUT.PUT_LINE(‘SQL ID: ‘ || cur_rec.SQL_ID);

DMBS_OUTPUT.PUT_LINE(‘SQL TEXT: ‘ || substr(v_sqltext,1,4000));

DMBS_OUTPUT.PUT_LINE(‘DURING: ‘ || cur_rec.DURING || ‘ seconds’);

DMBS_OUTPUT.PUT_LINE(‘.......................’);

END;

END LOOP;

END;

/SPOOL OFF

EOF

############

#############################3

# if the mail file exists, then we can email this mail to the team

If [ -f $(MAILFILE) ] ; then

RES=’cat $MAILFILE | wc -l’

# echo “Res = $RES”

If [ $RES -gt 0 ]

then

cat $MAILFILE | mail -s “SQL Query running more than 2 mins” $MAIL_RECIPIENT

fi

fi