set pagesize 0;

set linesize 20;

set head off;

set feedback off;

spool $tmpfile

select trim(p.spid) from v\$process p,v\$session s

where s.paddr=p.addr

and s.status='SNIPED' and s.type='USER';

spool off

EOF

for x in `cat $tmpfile | grep "^[0123456789]"`

do

kill -9 $x

done

rm $tmpfile

monitor session 的脚本：

session_monitor_full.sh:

#!/bin/sh

ORACLE_BASE=/ora102;export ORACLE_BASE

ORACLE_HOME=/ora102/10.2;export ORACLE_HOME

ORACLE_SID=STCSMES;export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin;export PATH

log=/tmp/session_monitor_`date +%y%m%d`.xls

cd /ora102/scripts

rm session_monitor.xls

sqlplus -s ' / as sysdba' @session_monitor.sql

echo "######################################################################################" >> $log

cat session_monitor.xls >> $log

SUBJ="`hostname` Session monitor !"

uuencode /ora102/scripts/session_monitor.xls session_monitor.xls | mail -s "$SUBJ" "$1"

#mail -s "$SUBJ" "$1" < /ora102/scripts/session_monitor.xls

Exit

Session_monitor.sql:

set linesize 200

set term off verify off feedback off pagesize 999

set markup html on entmap ON spool on preformat off

spool session_monitor.xls

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') Current_Time from dual;

select machine,program,count(*) from v$session where username='SMP'  group by machine,program order by machine desc;

spool off

exit

再定制计划任务crontab即可！！