RE: Session Killed...!!

From: Luis Fernando Cerri <lcerri@xxxxxxxxxxxxxxxxxxxxxxx>
To: 'Oracle-L Freelists' <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 15 Sep 2005 15:43:13 -0300
For my development databases running on unix-like systems, I created a
stored procedure allowing developers to kill their own sessions. The stored
procedure has an exception to catch the error ORA-00031(session marked for
kill), so when it happens, it calls a shared lib that kills the process id
in the OS.
 
Sorry for the messages and coments in portuguese, but I'm sure all you can
understand the idea:
 
----> cut here and break your display<----
 
create or replace procedure p_killme(vsid in number, vserial# in number) as
    vusername varchar2(100) := '';
    vpid integer := 0;
    session_marked_for_kill exception;
    pragma exception_init(session_marked_for_kill,-31);
begin
    select s.username,p.spid
      into vusername,vpid 
      from v$session s,v$process p 
     where s.sid=vsid 
       and s.serial#=vserial# 
       and s.paddr = p.addr;
    if vusername = user then
       begin
         execute immediate 'alter system kill session
'''||vsid||','||vserial#||''''; -- mata a sessao
         exception
           when session_marked_for_kill then
               sys.p_killpid(vpid);
-- mata o processo referente a sessao
       end;
    else
       raise_application_error(-20902,'Impossivel matar sessoes de outro
usuario que nao o '||user||'.');
    end if;
    exception
      when no_data_found then
       raise_application_error(-20903,'Nao existe uma sessao com essas
caracteristicas.');
end;
 
--> end cut <---
 
[]s
Luis

   

-----Original Message-----
From: Chen, Sarah [mailto:Sarah_Chen@xxxxxxxxx] 
Sent: quinta-feira, 15 de setembro de 2005 11:54
To: 'Thomas.Mercadante@xxxxxxxxxxxxxxxxx'; 'chiragdba@xxxxxxxxx'; 'Oracle-L
Freelists'
Subject: RE: Session Killed...!!


I always check process id as well as sid and serial# while killing a session
from Oracle.
 
I will always query v$session before and after, and if the status of
v$session marked "killed", and then I will go ahead kill OS process to clean
this session. It always works on Solaris.
 
Sarah

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Mercadante, Thomas F (LABOR)
Sent: Thursday, September 15, 2005 10:47 AM
To: chiragdba@xxxxxxxxx; Oracle-L Freelists
Subject: RE: Session Killed...!!



If you kill the unix process associated with this session first, and then
the session it will go away.

 

Killing sessions in Oracle has always been weird.  Sometimes they go away
and sometimes not, depending on OS.  In Windows, use orakill to kill the
session - this always seems to work in windows.  And killing the unix
session then the Oracle session seems to work just fine in AIX.

 


  _____  


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Chirag DBA
Sent: Thursday, September 15, 2005 10:36 AM
To: askdba@xxxxxxxxxxxxx; Oracle-L Freelists
Subject: Session Killed...!!

 

Hi ,

 

I saw many users on my database connected for more than 6 days without any
activity.

 

I killed 1 user and still the status in v$session is showing as killed.

 

but it it not getting removed from the v$session.

 

I am running 9.0.1.3 <http://9.0.1.3>  on solaris.

 

any idea? We already hit the bug ora-07442.

 

regards - chirag
RE: Session Killed...!!

Other related posts:
