Home » RDBMS Server » Server Administration » killed session still in v$session but not in v$process (oracle 9.2.0.4 on AIX 5L)
killed session still in v$session but not in v$process [message #318221] Tue, 06 May 2008 02:48 Go to next message
lokeshonline
Messages: 29
Registered: April 2006
Junior Member
I killed two sessions through OEM after that I killed through SQL session. These two session are still being shown if I query v$session but not shown if I see in OEM. Also if I query select sid,serial#,status from v$session where paddr not in (select addr from v$process) it shows me these two sessions with status KILLED. Ideally the above query should not show any record as for every session in v$session there should be an OS process with a recrod in v$process.
I have seen alert log but there is no error.

Please help why these two sessions are there without any corresponding record in v$process and what should I do to kill them properly.



Re: killed session still in v$session but not in v$process [message #318233 is a reply to message #318221] Tue, 06 May 2008 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You just have to wait that PMON cleans up the sessions.
You can't do anything more.

Regards
Michel
Re: killed session still in v$session but not in v$process [message #318242 is a reply to message #318221] Tue, 06 May 2008 03:54 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Try killing OS process - on Linux/Solaris

kill -9 pid (pid= spid from v$process)

Regards,

MSMallya
Re: killed session still in v$session but not in v$process [message #318244 is a reply to message #318242] Tue, 06 May 2008 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read the topic.
1/ Process already no more existed. There is nothing in v$process
2/ Your command does not remove the session which is the purpose of the topic

Regards
Michel
Re: killed session still in v$session but not in v$process [message #318246 is a reply to message #318244] Tue, 06 May 2008 04:01 Go to previous messageGo to next message
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
Member
Yes there are no info available in v$process once you kill the session.

But my suggestion for - in future whenver killing the session, one can note down the "SPID" inorder to kill OS process, if required.

MSMallya
Re: killed session still in v$session but not in v$process [message #342995 is a reply to message #318244] Mon, 25 August 2008 15:52 Go to previous message
randbell
Messages: 1
Registered: August 2008
Location: Oregon
Junior Member
I suspect what MSMallya was referring too was that when you have a "KILLED" session in v$session there may still be an oracle backend server process running on the database server.

If you look for the spid in v$process before you kill the session you can be sure to be able to find that backend server process and send a kill signal to it in case the process gets stuck in a "KILLED" status.

In my experience I've seen processes that were killed because they were hung not die and linger on in a "KILLED" state and at least in earlier versions of Oracle the v$process record would no longer exist or not report an SPID. At this point I would be forced to do the very tedious task of listing all the active SPIDs from v$process and comparing that list to a /bin/ps -eaf | grep oracle list of unix pids and find the Oracle process that is still in unix but no longer reported in v$process. Once found I'd kill this process and PMON would make short work of the v$session entry that was marked for kill.

This is why I always try to check the spid before I kill a session that is hung - it can pay off to be able to kill the backend server process quickly if you need to release whatever resource is being held by that hung session. Note that the join condition between v$session is v$session.paddr = v$process.addr so I would often do the following:
select vs.sid, vs.serial#, vp.spid
from v$session vs,
v$process vp
where vs.sid=<SID>
and vs.paddr = vp.addr
;


In order to get the info I need to kill the session. (note that I free handed that - there may be syntax errors)

Obviously all bets are off if this is a shared server (eg. MTS) implementation - I've mostly been working with data warehousing lately so I haven't had to deal with that situation.

Previous Topic: insufficient privileges when trying to connect
Next Topic: upgrade/migrate (merged)
Goto Forum:
  


Current Time: Tue Dec 06 02:21:23 CST 2016

Total time taken to generate the page: 0.09322 seconds