To clean up the sniped sessions faster, you can also use the IMMEDIATE
clause (new with 8i, I believe) in the ALTER SYSTEM KILL SESSION command.
See docs for details.
HTH
Srini Chavali
Oracle DBA
Cummins Inc
[EMAIL PROTECTED]@fatcity.com on 01/15/2002 02:05:23 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
appreciate you help guys..
-----Original Message-----
Sent: Tuesday, January 15, 2002 9: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:
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:
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).
