| Killing a session in KILLED status [message #54740] |
Mon, 02 December 2002 13:17  |
Pakshi
Messages: 6
Registered: November 2002
|
Junior Member |
|
|
I have a session running in database1 which is connecting to database2 using DB Link. I want to kill that session since it does not seem to be doing anything. I executed the ALter system kill session command on database2 the session status got changed from "INACTIVE" to "KILLED". Then I executed the same command to kill the session in database1. Now the sessions in both the databases are in "KILLED" status but it does not go off from v$session table. What do I do to ensure that the session goes from v$session table. I do not mind if the session is rolled back.
Suggest.
Thanks.
Pakshi
|
|
|
|
| Re: Killing a session in KILLED status [message #54741 is a reply to message #54740] |
Mon, 02 December 2002 14:59  |
sharan alva
Messages: 57
Registered: November 1999
|
Member |
|
|
You have a session that you have killed, but it seems as though it will not go away:
SVRMGRL> alter system kill session 'sid, serial#';
SVRMGRL> select status, username from v$session;
status killed
username username
You have issued this several times and it seems it still is marked as killed,
but has a lock on the existing table.
In order to determine which process to kill:
a) On a Unix platform:
SVRMGRL> SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1
FROM v$session
WHERE paddr = addr);
% kill <spid>
b) On a Windows platform:
SVRMGRL> SELECT spid, osuser, s.program
FROM v$process p, v$session s
WHERE p.addr=s.paddr;
Then use orakill at the DOS prompt:
c:>orakill sid thread
Explanation:
============
PMON has not yet cleaned up the process and this may not happen for several
hours.
By finding the spid you can then force the process to be killed instead of
waiting for PMON to wake up and kill it.
When issueing the 'kill' command be sure that you kill "DEDICATED SERVER
PROCESSES", those called:
oracleXXXX (local=NO)
where XXX is the ORACLE_SID.
Be sure you do not kill processes such as:
d000NNN
s000000
pmon_NNN
- Hope this helps..
|
|
|
|
| Re: Killing a session in KILLED status [message #54744 is a reply to message #54741] |
Mon, 02 December 2002 15:12 |
Pakshi
Messages: 6
Registered: November 2002
|
Junior Member |
|
|
|
Thanks for your reply. I executed the kill session statements 5 days back in both the databases the database1 and database2 but the session status is KILLED in both the databases. We have Oracle running in SOLARIS box.
|
|
|
|
| Re: Killing a session in KILLED status [message #54756 is a reply to message #54740] |
Tue, 03 December 2002 07:49 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
Once the process is KILLED, it is killed. No worry. What you see in v$session is becoz the PMON has not cleared it and it takes it's own time to do so. This should hang around for hours, not days.
But the bottom line is that the process is killed and your resource is not being taken away. Try to ignore them, if you can.
Cheers.
|
|
|
|
|
|
| Re: Killing a session in KILLED status [message #54779 is a reply to message #54756] |
Wed, 04 December 2002 09:10 |
Sanjay
Messages: 236
Registered: July 2000
|
Senior Member |
|
|
Man, that was interesting.
I have never killed a session with kill -9 at OS level after that session has been marked as killed. But I have killed at OS level to get the server down or in the extreme when the shared memory is locked ... and so on.
When I get a chance to monitor some 'killed' sessions hanging around, I am going to check the OS entries as well. In my case, nearly all the killed session have dissapeared instantly, if not after a while.
Later.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
