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 Mercadante, Thomas F (LABOR)

Reply: << 11 12 13 14 15

Chirag,

Use the following:

select vp.pid,vs.sid,spid,vs.program

from v$process vp,v$session vs

where vs.paddr=vp.addr

The spid column is the unix process id.

So to kill a session, I issue a kill -9 the spid value, then issue a
kill session within Oracle.

Tom

__ ____ ____ ____ ____ ____ ____

From: Chirag DBA [mailto:chiragdba@(protected)]
Sent: Thursday, September 15, 2005 11:08 AM
To: yaoyongping@(protected)
Cc: Sarah_Chen@(protected); Mercadante, Thomas F (LABOR); Oracle-L
Freelists
Subject: Re: Session Killed...!!

how to find the os process associated with it?

so that you can do kill -9 @#@#.

regards - Chirag

On 9/15/05, Yongping Yao <yaoyongping@(protected)> wrote:

I agree that it's very strange to kill a session in Oracle. As I
experienced, SELECTs are more easier to kill than DMLs and SELECTs
running after a long time are more difficult to kill. UPDATE is the
most difficult to kill. Oracle always reponds as "we have marked the
session killed" but the session is still there and the lock remained
hold. Anyone with good recommendations?

2005/9/15, Chen, Sarah <Sarah_Chen@(protected)>:
>
> 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) <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) <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 on solaris.
>
>
>
>
>
> any idea? We already hit the bug ora-07442 (See ora-07442.ora-code.com).
>
>
>
>
>
> regards - chirag

--
Yao Yongping
Learning Oracle, UNIX/Linux...
Love Reading, Classical Music, Philosophy, Economics etc.
Blog: http://blog.csdn.net/ern
http://spaces.msn.com/members/yaoyp/
<http://spaces.msn.com/members/yaoyp/>

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft
-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:st1=
"urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC
-html40">

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>

<style>

</style>

</head>

<body lang=EN-US link=blue vlink=blue>

<div class=Section1>

Chirag,<o:p></o:p>

<o:p> </o:p>

Use the following:<o:p></o:p>

<o:p> </o:p>

select
 vp.pid
,vs.sid,spid
,vs.program <o:p></o
:p>

from
 v$process vp,v$session vs<o:p></o:p>

where vs.paddr=vp
.addr<o:p></o:p>

<o:p> </o:p
>

The spid column
is the unix process id.<o:p></o:p>

<o:p> </o:p
>

So to kill a
session, I issue a kill -9 the spid value, then issue a kill session within
Oracle.<o:p></o:p>

 
Tom<o:p></o:p>

<o:p> </o:p>

<div>

<div class=MsoNormal align=center style='text-align:center'>

<hr size=2 width="100%" align=center tabindex=-1>

</div>

From: Chirag DBA
[mailto:chiragdba@(protected)] 
Sent: Thursday, September 15, 2005
11:08 AM 
To: yaoyongping@(protected) 
Cc: Sarah_Chen@(protected);
Mercadante, Thomas F (LABOR); Oracle-L Freelists 
Subject: Re: Session Killed...!!<o:p></o:p>

</div>

<o:p> </o:p>

<div>

how to find the os  process associated with it?<o:p></o:p>

</div>

<div>

 <o:p></o:p>

</div>

<div>

so that you can do kill -9 @#@#.<o:p></o:p>

</div>

<div>

 <o:p></o:p>

</div>

<div>

regards - Chirag 
 <o:p></o:p>

</div>

<div>


On 9/15/05, Yongping
Yao <<a href="mailto:yaoyongping@(protected)">yaoyongping@(protected)
</a>>
wrote: <o:p></o:p>

I agree that it's very strange to kill a session in Oracle. As I 
experienced, SELECTs are more easier to kill than DMLs and SELECTs 
running after a long time are more difficult to kill. UPDATE is the 
most difficult to kill. Oracle always reponds as "we have marked the 
session killed" but the session is still there and the lock remained 
hold. Anyone with good recommendations? 
 
2005/9/15, Chen, Sarah <<a href="mailto:Sarah_Chen@(protected)">Sarah_Chen
@(protected)</a>>: 
> 
> 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: <a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce
@(protected)</a>
[mailto:<a href="mailto:oracle-l-bounce@(protected)">
oracle-l-bounce@(protected)</a>]On 
> Behalf Of Mercadante, Thomas F (LABOR) 
> Sent: Thursday, September 15, 2005 10:47 AM 
> To: <a href="mailto:chiragdba@(protected)">chiragdba@(protected)</a>; 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: <a href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce
@(protected)</a>
[mailto:<a href="mailto:oracle-l-bounce@(protected)">
oracle-l-bounce@(protected)</a>] 
> On Behalf Of Chirag DBA 
> Sent: Thursday, September 15, 2005 10:36 AM 
> To: <a href="mailto:askdba@(protected)">askdba@(protected)</a>;
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 <a href="http://9.0.1.3">9.0.1.3</a> on solaris. 
> 
> 
> 
> 
> 
> any idea? We already hit the bug ora-07442 (See ora-07442.ora-code.com). 
> 
> 
> 
> 
> 
> regards - chirag 
 
 
-- 
<st1:City w:st="on"><st1:place w:st="on">Yao</st1:place></st1:City> Yongping 
Learning Oracle, UNIX/Linux... 
Love <st1:City w:st="on"><st1:place w:st="on">Reading</st1:place></st1:City>,
Classical Music, Philosophy, Economics etc. 
Blog: <a href="http://blog.csdn.net/ern">http://blog.csdn.net/ern</a> 
        <a
href="http://spaces.msn.com/members/yaoyp/">
http://spaces.msn.com/members/yaoyp/</a><o:p></o:p>

</div>

<o:p> </o:p>

</div>

</body>

</html>