| killed session still in v$session but not in v$process [message #318221] |
Tue, 06 May 2008 02:48  |
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 #342995 is a reply to message #318244] |
Mon, 25 August 2008 15:52  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
