Re: Killing sessions

From: <yitbsal_at_statcan.ca>
Date: 2000/08/04
Message-ID: <8mf2oh$5iq$1@nnrp1.deja.com>#1/1

In article <3989907E.63E29A57_at_protek.ru>,   Ushkalo Igor <igorus_at_protek.ru> wrote:
> stuarti wrote:
> >
> > Why does the session still appear in instance manager even and
> > it has been killed ?? marked as killed
>
> killed sessions can hang even till 24 hours... suxx.
> if you really wanna kill session use V$PROCESS view to determine
> processID and kill it from unix shell by kill command.
>

And the following script will kill the Oracle session and the Unix process for you:

-----cut here------
/*

| killsession.sql To kill a user session
|                 Input OS ID
| HN130799

*/
set pause off
--DEFINE OSID="&&1"

column "NAME" format a10
column "program" format a25
column "Process" format a9
column "Logon" format a18
column "Stat" format a10

column ID format 999

column "Serial#" format 999999999
column "NAME" format a15
column "osuser" format a15
column "OSID" format 9999

column SER new_value _SER
column ID new_value _ID

column "NAME" new_value _NAME
column "osuser" new_value _OSU
column "OSID" new_value _OSID

set lines 132
break on "Stat"
TTITLE "User session status at &_TIME"

ACCEPT OSID     PROMPT  'Enter session OS ID or null : '
ACCEPT OSUSR    PROMPT  '      O/S Username or null  : '
ACCEPT username PROMPT  '      Username or Null      : '

select s.osuser ,s.username "NAME",s.sid ID,s.serial# SER,p.spid "OSID",s.process "Process",

       substr(s.program ,1,20) "program",s.status "Stat" from v$process p, v$session s
where p.addr=s.paddr
and (s.username='OPS$'||upper('&&USERNAME') or s.username = upper('&&USERNAME') or '&&USERNAME' is null) and (s.osuser = ('&&OSUSR') or '&&OSUSR' is null) and ( p.spid = '&&OSID' or '&&OSID' is null) order by 1,2,3
/
column ID new_value _ID

PROMPT '*********************************************************'
PROMPT ' Select ID from above list or hit return to kill all     '
PROMPT '*********************************************************'
ACCEPT   ID     PROMPT  'Enter ID or null : '

ACCEPT OK PROMPT "Confirm selection (Y/N) " ttitle off
set termout off pause off echo off verify off heading off heads off feed off
spool &&dbr.kill_&&_OSU..sql
select 'PROMPT Terminating session: '||s.sid||' Serial#: '||s.serial#||

        ' OSUser:'||s.osuser||' OraUser: '||s.username||chr(10)|| 'alter system kill session '''||s.sid||','||s.serial#||''';' from v$process p, v$session s
where p.addr=s.paddr
and (s.username='OPS$'||upper('&&USERNAME') or s.username = upper('&&USERNAME') or '&&USERNAME' is null)

and (s.osuser = ('&&OSUSR') or '&&OSUSR' is null)
and ( p.spid = '&&OSID' or '&&OSID' is null)
and ( s.sid like decode('&&ID',null,s.sid,'&&ID'))

and '&&OK' in ('Y','y');
spool off
spool &&dbr.kill_&&_OSU..sh
select 'echo Terminating session: '||s.sid||' Serial#: '||s.serial#||

        ' OSUser:'||s.osuser||' OraUser: '||s.username||chr(10)||
        'kill -9 '||p.spid

from v$process p, v$session s
where p.addr=s.paddr
and (s.username='OPS$'||upper('&&USERNAME') or s.username = upper('&&USERNAME') or '&&USERNAME' is null)

and (s.osuser = ('&&OSUSR') or '&&OSUSR' is null)
and ( p.spid = '&&OSID' or '&&OSID' is null)
and ( s.sid like decode('&&ID',null,s.sid,'&&ID'))

and '&&OK' in ('Y','y');
spool off

set termout on verify on heading on heads on pagesize 30 feed on

@&&dbr.kill_&&_OSU
!chmod +xxx &&dbr.kill_&&_OSU..sh
!&&dbr.kill_&&_OSU..sh

-----cut here------

Salaam Yitbarek

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Aug 04 2000 - 00:00:00 CDT