here is a little script i wrote
simpliefied a little
it first kills the session within oracle then generates the unix commands to
kill the session
so there can never a mistake about killing the wrong pid
and i also insert into an oracle table all killed sessions so you can query
them a litlle easier
-----------kill_sniped_session.ksh-------------------
#!/bin/sh
#CREATE TABLE killed_sessions
# (
# SID NUMBER,
# SERIAL# NUMBER,
# SPID VARCHAR2(9),
# USERNAME VARCHAR2(30),
# TIMESTAMP DATE
# ) tablespace DBA
# STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0);
tmpfile=/tmp/kill_sniped.$$
su - superman -c "sqlplus -s /@mer << 'EOF'
whenever sqlerror exit 1
whenever oserror exit 1
set serveroutput on
set feedback off
spool $tmpfile
DECLARE
Stmt_Str VARCHAR2(200);
v_Sid v\$session.sid%TYPE;
v_Serial v\$session.serial#%TYPE;
v_Username v\$session.username%TYPE;
v_spid v\$process.spid%TYPE;
CURSOR c_list IS
select a.SID, a.SERIAL#, a.USERNAME, b.SPID
from v\$session a, v\$process b
where a.paddr = b.addr
and a.STATUS = 'SNIPED';
BEGIN
FOR user in c_list
LOOP
v_sid := user.sid;
v_serial := user.serial#;
v_username := user.username;
v_spid := user.spid;
stmt_str := 'alter system kill session ''' || v_sid || ',' ||
v_serial || '''';
insert into sys.killed_sessions values(v_sid, v_serial, v_spid,
v_username, sysdate);
DBMS_OUTPUT.PUT_LINE('kill -9 '|| v_spid);
Execute Immediate(stmt_str);
END LOOP;
END;
/
EOF" >>/dev/null 2>&1
cat $tmpfile |while read line ; do
cmd=$line
eval $cmd
done
rm -f $tmpfile
--------------------
Thanks
David Hill
DBA
Le Chateau Stores
-----Original Message-----
Sent: Tuesday, January 15, 2002 10:06 AM
To: Multiple recipients of list ORACLE-L
Attached are the scripts we use to kill sniped sessions. This is part of a
series of monitoring scripts we developed for our Oracle databases. The
scripts are scheduled through Cron and run every 15 minutes. A logonid or
an email address is passed to the scripts for either paging or email. In
this case an accum of sniped sessions is maintained on a 'flat' file just so
we can go back and check to see how many sniped sessions are being killed.
I will attach a zip file. If that doesn't get through I will also list the
scripts below.
Ron Smith
Kerr-McGee Corp
SNIPED.SH
#! /bin/sh
# DBA MONITORING SCRIPTS
# ******************************************************************
#
# Author: Ron Smith
# Date: 06/18/98
# Funtion: Checks for sessions that have been "Sniped".
#
# ******************************************************************
#
# CHANGE HISTORY
#
# DATE WHO Reason for Change
# 03/03/00 Ron Smith New Prog
#
# ******************************************************************
#
# FUNCTION
#
# This script calls sniped.sql.
# The function of this script is to report sessions that have
# been "sniped" by Oracle through the use of resource limits.
#
# If the id of the DBA is a Zid, a page will be sent. If the
# id of the DBA is an email address (determined by looking for
# an "@" ) , an EMAIL will be sent.
#
# ******************************************************************
#
# PREREQUISITES
#
# The OPS$ORACLE user must exist in the instance. This can be
# created by running the opsuer.sql script in SQLPLUS while
# logged on as SYSTEM.
#
# The cdmonitoring script must exist in the home/oracle
# directory.
#
# ******************************************************************
#
# RUN SYNTAX
#
# sniped.sh (sid) (oncall dba)
#
#
# ******************************************************************
# cd to the monitoring script directory
. $HOME/cdmonitoring.sh
ORACLE_SID=$1
export ORACLE_SID
DBA=$2
export DBA
echo $DBA
ATCNT=`echo $DBA | grep @ | wc -l`
export ATCNT
ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d: -f2`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH:.
export PATH
# Delete the old list file if it exists
if [ -e sniped_$ORACLE_SID.lst ]
then rm sniped_$ORACLE_SID.lst
fi
# Delete the old error file if it exists
if [ -e sniped_$ORACLE_SID.err ]
then rm sniped_$ORACLE_SID.err
fi
# Delete the old kill file if it exists
if [ -e sniped_kill_$ORACLE_SID.sh ]
then rm sniped_kill_$ORACLE_SID.sh
fi
# If sending to EMAIL address, run sql with headings on else run with
headings off
if [ "$ATCNT" -gt "0" ]
then
sqlplus / @sniped.sql on
else
sqlplus / @sniped.sql off
fi
# If there is anything in the lst file then kill the user processes and send
a message
if [ -s sniped_$ORACLE_SID.lst ]
then cat sniped_$ORACLE_SID.lst >> sniped_accum.lst
sqlplus / @sniped2.sql
if [ -s sniped_kill_$ORACLE_SID.sh ]
then chmod +x sniped_kill_$ORACLE_SID.sh;
cat sniped_kill_$ORACLE_SID.sh >> sniped_kill_accum.lst
./sniped_kill_$ORACLE_SID.sh;
fi
echo "-DBA- Sniped sessions killed " > sniped_$ORACLE_SID.err
echo "SID=" $ORACLE_SID " " >> sniped_$ORACLE_SID.err
cat sniped_$ORACLE_SID.lst >> sniped_$ORACLE_SID.err
if [ "$ATCNT" -gt "0" ]
then
echo "email sent"
elm -s "-DBA- $ORACLE_SID Sniped sessions exist" $DBA <
sniped_$ORACLE_SID.err
else
LC=`cat sniped_$ORACLE_SID.lst | sed -e 's/ */ /g' | wc -c`
echo $LC
if [ "$LC" -gt "160" ]
then echo "Sniped sessions killed. Check
sniped_$ORACLE_SID.lst" >> sniped_$ORACLE_SID.err
else
cat sniped_$ORACLE_SID.lst >> sniped_$ORACLE_SID.err
fi
echo "page sent"
pager $DBA "`cat sniped_$ORACLE_SID.err`"
fi
fi
SNIPED.SQL
set pause off
SET ECHO off
set verify off
set feedback off
set linesize 132
set heading &1
col "User Name" format a10;
alter session set nls_date_format = 'dd-MON-yyyy hh24:mi:ss';
spool sniped_$ORACLE_SID.lst
select s.username "User Name",
s.osuser "OS User",
s.status "Status",
s.logon_time "Connect Time",
p.spid, p.pid, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
and status = 'SNIPED';
spool off;
exit;
SNIPED2.SQL
set pause off
SET ECHO off
set verify off
set feedback off
set linesize 132
set heading off
spool sniped_kill_$ORACLE_SID.sh;
select 'kill -9 ' || p.spid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
and status = 'SNIPED';
spool off;
exit;
-----Original Message-----
Sent: Monday, January 14, 2002 4:10 PM
To: Multiple recipients of list ORACLE-L
Dear List,
I have "init.ora" resource_limit = true, and idle_time set in profile.
When idle_time exceeds, the session status becomes 'sniped' in v$session and
never gets cleaned up.
I manually kill these threads using orakill utility.
I appreciate, if someone can help me with a script to clean up these
sessions automatically(8.1.7 on NT)??
Sunil Nookala
DBA
Dell Computer Corp.
Austin, TX 78738
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: david hill
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
