RE: Session Killed...!!

From: Luis Fernando Cerri <lcerri_at_santanderbanespa.com.br>
Date: Thu, 15 Sep 2005 15:43:13 -0300
Message-ID: <A70750EC2FDE6A4C98FCA68CC6AC28330275A743@bsbrsp42.bs.br.bsch>

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_at_BUDCO.com] Sent: quinta-feira, 15 de setembro de 2005 11:54 To: 'Thomas.Mercadante_at_labor.state.ny.us'; 'chiragdba_at_gmail.com'; '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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mercadante, Thomas F (LABOR)
Sent: Thursday, September 15, 2005 10:47 AM To: chiragdba_at_gmail.com; 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chirag DBA
Sent: Thursday, September 15, 2005 10:36 AM To: askdba_at_freelists.org; 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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 15 2005 - 13:45:25 CDT