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 (See ORA-00031.ora-code.com)(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@(protected)] Sent: quinta-feira, 15 de setembro de 2005 11:54 To: 'Thomas.Mercadante@(protected)'; 'chiragdba@(protected)'; '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@(protected) [mailto:oracle-l-bounce@(protected)]On Behalf Of Mercadante, Thomas F (LABOR) Sent: Thursday, September 15, 2005 10:47 AM To: chiragdba@(protected); 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@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Chirag DBA Sent: Thursday, September 15, 2005 10:36 AM To: askdba@(protected); 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.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v = "urn:schemas-microsoft-com:vml" xmlns:o = "urn:schemas-microsoft-com:office:office" xmlns:w = "urn:schemas-microsoft-com:office:word"><HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"> <TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1505" name=GENERATOR><!--[if !mso]> <STYLE>v\:* { BEHAVIOR: url(#default#VML) } o\:* { BEHAVIOR: url(#default#VML) } w\:* { BEHAVIOR: url(#default#VML) } .shape { BEHAVIOR: url(#default#VML) } </STYLE> <![endif]--> <STYLE>@(protected) { font-family: Tahoma; } @(protected) Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; } P.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } LI.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } DIV.MsoNormal { FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman" } A:link { COLOR: blue; TEXT-DECORATION: underline } SPAN.MsoHyperlink { COLOR: blue; TEXT-DECORATION: underline } A:visited { COLOR: blue; TEXT-DECORATION: underline } SPAN.MsoHyperlinkFollowed { COLOR: blue; TEXT-DECORATION: underline } SPAN.EmailStyle17 { COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply } DIV.Section1 { page: Section1 } </STYLE> </HEAD> <BODY lang=EN-US vLink=blue link=blue> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2>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 (See ORA-00031.ora-code.com)(session marked for kill), so when it happens, it calls a shared lib that kills the process id in the OS.</FONT></SPAN></DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2>Sorry for the messages and coments in portuguese, but I'm sure all you can understand the idea:</FONT></SPAN></DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2>----> cut here and break your display<----</FONT></SPAN></DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2 >create or replace procedure p_killme(vsid in number, vserial# in number) as<BR> vusername varchar2(100) := '';<BR> vpid integer := 0;<BR> session_marked_for_kill exception;<BR> pragma exception_init(session_marked_for_kill,-31);<BR>begin<BR> select s.username,p.spid<BR> into vusername,vpid <BR> from v$session s,v$process p <BR> where s.sid=vsid <BR> and s.serial#=vserial# <BR> and s.paddr = p.addr;<BR> if vusername = user then<BR> begin<BR> execute immediate 'alter system kill session '''||vsid||','||vserial#||''''; -- mata a sessao<BR> exception<BR> when session_marked_for_kill then<BR>   ; sys.p_killpid(vpid);   ; -- mata o processo referente a sessao<BR> end;<BR> else<BR> raise_application_error(-20902,'Impossivel matar sessoes de outro usuario que nao o '||user||'.');<BR> end if;<BR> exception<BR> when no_data_found then<BR> raise_application_error(-20903, 'Nao existe uma sessao com essas caracteristicas.');<BR>end;</FONT></SPAN></DIV> <DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2>--> ; end cut <---</FONT></SPAN></DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2></FONT></SPAN> </DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2>[]s</FONT></SPAN></DIV> <DIV><SPAN class=252352918-15092005><FONT face=Arial color=#0000ff size=2>Luis</FONT></SPAN></DIV><!-- Converted from text/rtf format --> <P><SPAN lang=pt-br><FONT face=Arial></FONT></SPAN> <SPAN lang=pt-br><FONT face=Arial> </FONT></SPAN> </P> <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV></DIV> <DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT face=Tahoma size=2>-- --Original Message-- --<BR><B>From:</B> Chen, Sarah [mailto:Sarah_Chen@(protected)] <BR><B>Sent:</B> quinta-feira, 15 de setembro de 2005 11:54<BR><B>To:</B> 'Thomas.Mercadante@(protected)'; 'chiragdba@(protected)'; 'Oracle-L Freelists'<BR><B>Subject:</B> RE: Session Killed...!!<BR><BR></FONT></DIV> <DIV><SPAN class=372365014-15092005><FONT face=Garamond color=#000080>I always check process id as well as sid and serial# while killing a session from Oracle.</FONT></SPAN></DIV> <DIV><SPAN class=372365014-15092005><FONT face=Garamond color=#000080></FONT></SPAN> </DIV> <DIV><SPAN class=372365014-15092005><FONT face=Garamond color=#000080>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.</FONT></SPAN></DIV> <DIV><SPAN class=372365014-15092005><FONT face=Garamond color=#000080></FONT></SPAN> </DIV> <DIV><SPAN class=372365014-15092005><FONT face=Garamond color=#000080>Sarah</FONT></SPAN></DIV> <BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma size=2>-- --Original Message-- --<BR><B>From:</B> oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]<B>On Behalf Of </B>Mercadante, Thomas F (LABOR)<BR><B>Sent:</B> Thursday, September 15, 2005 10:47 AM<BR><B>To:</B> chiragdba@(protected); Oracle-L Freelists<BR><B>Subject:</B> RE: Session Killed...!!<BR><BR></FONT></DIV> <DIV class=Section1> <P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">If you kill the unix process associated with this session first, and then the session it will go away.<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p>< /SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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.<o:p></o:p></SPAN></FONT></P> <P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p>< /SPAN></FONT></P> <DIV> <DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <HR tabIndex=-1 align=center width="100%" SIZE=2> </SPAN></FONT></DIV> <P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN ></FONT></B><FONT face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma"> oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] <B><SPAN style="FONT-WEIGHT: bold">On Behalf Of </SPAN></B>Chirag DBA<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Thursday, September 15, 2005 10:36 AM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B> askdba@(protected); Oracle-L Freelists<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Session Killed...!!</SPAN></FONT><o:p></o:p></P></DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">Hi ,<o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">I saw many users on my database connected for more than 6 days without any activity.<o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">I killed 1 user and still the status in v$session is showing as killed.<o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">but it it not getting removed from the v$session.<o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">I am running <A href="http://9.0.1.3">9.0.1.3</A> on solaris.<o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">any idea? We already hit the bug ora-07442 (See ora-07442.ora-code.com).<o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt"> <o:p></o:p></SPAN></FONT></P></DIV> <DIV> <P class=MsoNormal><FONT face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">regards - chirag<o:p></o:p></SPAN></FONT></P></DIV></DIV></BLOCKQUOTE></BLOCKQUOTE>< /BODY></HTML>
