使用该脚本可以快速看到数据库当前的等待事件及连接数信息,可在脚本基础上继续扩展,添加支持的参数,如表空间查看等。当然判断参数传入也可以通过分支语句实现。
#created by sunhailong at 20120615
function cmd_help {
echo "$0 --help"
echo "$0 -h"
echo "$0 -count"
echo "$0 -event"
}
function event {
sqlplus /nolog<<EOF
conn / as sysdba
set lines 200 pages 999
spool /tmp/session_mon_event0616.out app
set lines 200 pages 999
col machine for a10
col event for a30
col osuser for a10
select sid,serial#,inst_id,event,sql_id,machine,last_call_et esca_time,osuser,username ora_user from gv\$session where type not like 'BACK%' and status='ACTIVE' and wait_class<>'Idle' and username<>'SYS' order by 5;
spool off
EOF
}
function count {
sqlplus /nolog<<EOF
conn / as sysdba
--set lines 200 pages 999
spool /tmp/session_mon_count0616.out app
--set lines 200 pages 999
--select count(*),status,inst_id from gv\$session where type not like 'BACK%' group by status,inst_id order by status;
set feedback off lines 200 pages 999
col machine for a30
col event for a40
col p1 for 999999999999999
col p2 for 999999999999999
col FAILOVER_TYPE for a15
col FAILOVER_METHOD for a15
col FAILED_OVER for a15
select count(*) from gv\$session where type not like 'BACK%';
select count(*) active,inst_id from gv\$session where type not like 'BACK%' and status='ACTIVE' group by inst_id;
select sid,serial#,inst_id,event,sql_id,machine,last_call_et esca_time,osuser,username ora_user from gv\$session where type not like 'BACK%' and status='ACTIVE' and wait_class<>'Idle' and username<>'SYS' order by 5;
spool off
EOF
}
##################main#######################
if [ $# -ne 1 ]
then
echo "INPUT ONE PARAMETERS!"
help;
exit 77
fi
if [ $1 = "-event" ]
then
event;
elif [ $1 = "-count" ]
then
count;
#elif [ $1 = "--help" ]
elif [ $1 = "--help" ]||[ $1 = "-h" ]
then
cmd_help;
fi