oracle的sql monitor是一个很有用的工具集。但是通过sql命令和反复去调用dbms_tune来传入参数等等操作感觉挺费事的。

MONITOR_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID < set pages 100set linesize 200col status format a20col username format a15col module format a20col program format a25col sql_id format a20col sql_text format a20select sql_id,STATUS  ,  USERNAME  ,  MODULE ,   PROGRAM, substr(SQL_TEXT,0,20) sql_text from v\\$sql_monitor where username =upper('$1') group by sql_id,STATUS  ,  USERNAME  ,  MODULE ,   PROGRAM, substr(SQL_TEXT,0,20); exit; END` if [ -z "$MONITOR_OWNER" ]; then  echo "no object exists, please check again"  exit 0 else  echo '*******************************************' echo " $MONITOR_OWNER    "  echo '*******************************************'fi

脚本运行结果如下，可以显示sql_id和状态，还有简单的sql语句。

SQL_ID               STATUS               USERNAME        MODULE               PROGRAM                   SQL_TEXT              -------------------- -------------------- --------------- -------------------- ------------------------- --------------------  7u9gsk798bvrp        DONE (ALL ROWS)      TEST_USER         JDBC Thin Client     JDBC Thin Client          SELECT   AA.DATA_GROcjqdgd14xjwjm        DONE (ALL ROWS)      TEST_USER         JDBC Thin Client     JDBC Thin Client          SELECT TO_CHAR (SUBS2zymmn3s4xn1k        DONE (ALL ROWS)      TEST_USER         JDBC Thin Client     JDBC Thin Client          SELECT      nrg."Cyc1hg2wcuapy3y3        EXECUTING            TEST_USER         JDBC Thin Client     JDBC Thin Client          select d1_run_reque

如果要生成sql monitor报告。

MONITOR_OWNER=`sqlplus -silent $DB_CONN_STR@$SH_DB_SID < set pages 100set linesize 200col status format a20col username format a15col module format a20col program format a25col sql_id format a20col sql_text format a20select sql_id,STATUS  ,  USERNAME  ,  MODULE ,   PROGRAM, substr(SQL_TEXT,0,20) sql_text from v\\$sql_monitor where sql_id='$1' group by sql_id,STATUS  ,  USERNAME  ,  MODULE ,   PROGRAM, substr(SQL_TEXT,0,20) ; exit; END` if [ -z "$MONITOR_OWNER" ]; then  echo "no object exists, please check again"  exit 0 else  echo '*******************************************' echo " $MONITOR_OWNER    "  echo '*******************************************'fi sqlplus -silent $DB_CONN_STR@$SH_DB_SID < set long 99999set pages 0set linesize 200col status format a20col username format a30col module format a20col program format a20col sql_id format a20col sql_text format a50col comm format a200set long 999999SELECT dbms_sqltune.report_sql_monitor(sql_id => '$1',report_level => 'ALL',type=>'TEXT') comm FROM dual;  EOF

本文转自ICT时空dbasdk的博客，原文链接：，如需转载请自行联系原博主。