Session Killed...!!

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Session Killed...!!

Session Killed...!!

2005-09-15 - By Luis Fernando Cerri

Reply: << 11 12 13 14 15

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.

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 (See ora-07442.ora-code.com).

regards - chirag

<!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>
<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>           &nbsp
;  
sys.p_killpid(vpid);         &nbsp
;             
             
             
 
-- 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>--&gt
;
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>
<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>