Home » RDBMS Server » Server Administration » how to kill killed session
how to kill killed session [message #582844] Tue, 23 April 2013 01:52 Go to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
How to kill of a killed session?

I had killed inactive session using
alter system kill session 'sid,serial#';

after that that sessions marked as killed and still get in v$session.

Then i try to get spid so that i can killed then from os command kill -9 spid

but when i try to get that join with v$process, no row select.

select * from v$session where username ='CAS' and status ='KILLED'


return 205 records.


select p.*,s.username,s.program,s.machine,s.status from v$process p,v$session s 
where p.addr=s.paddr and s.status='KILLED'


return no record.




Re: how to kill killed session [message #582848 is a reply to message #582844] Tue, 23 April 2013 02:12 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

Hello mmi

Very good evening !

Hope you are Doing good !

First answer to you question like you are able to See the session Since Might be at that time the session Will be in the Marked as kill. after then the Pmon is responsible for clearing all the Processes.But mean while the time that is taken by PMON to clear the killed session's or Processes I mean here Will stay at that Instant of time.So to kill the session Immediate Please use the following syntax.

alter system kill session 'sid,serial#' immediate;

Please Feel free to get back to me

Regards,
Kapardi
Re: how to kill killed session [message #582850 is a reply to message #582848] Tue, 23 April 2013 02:17 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
i had also try to killed those killed session using immediate and record still get from v$session. and already 30 mins over but killed sessions still show in v$session. 30 minutes is not enough for clear it?


Re: how to kill killed session [message #582853 is a reply to message #582850] Tue, 23 April 2013 02:29 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
now i observed that session is clearly very slow.
select * from v$session where username ='CAS' and status ='KILLED'

it return now 185 rows, which was 205. But time interval is more than 45 minutes.
Re: how to kill killed session [message #582856 is a reply to message #582853] Tue, 23 April 2013 02:33 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

mmi

Are there any pending transaction or any active transaction for that session. Please check Via OEM.
Re: how to kill killed session [message #582857 is a reply to message #582856] Tue, 23 April 2013 02:42 Go to previous messageGo to next message
Roachcoach
Messages: 1194
Registered: May 2010
Location: UK
Senior Member
If you killed without immediate and then retried I'm pretty sure (it's been a while) that the subsequent immediate does nothing as its already killed.

Kill from the OS if you HAVE to have then killed asap and you forgot immediate.

[Updated on: Tue, 23 April 2013 02:42]

Report message to a moderator

Re: how to kill killed session [message #582858 is a reply to message #582857] Tue, 23 April 2013 02:50 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
@Roachcoach, i understand after marked kill, then alter system kill session do nothing. But my main concern is in v$session it show killed for a longer time, how to kill those killed session. One way is os kill using kill -9 spid, but when i try to find out spid, i failed. I try to join with v$process for spid, but no row return for those killed session.

I also try to find out active transaction using v$transaction view, but v$transaction has no record.
Re: how to kill killed session [message #582859 is a reply to message #582858] Tue, 23 April 2013 02:53 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

mmi

What Is the last_et_call for the killed session can you please paste me the output here.

Regards
kapardi

[Updated on: Tue, 23 April 2013 02:53]

Report message to a moderator

Re: how to kill killed session [message #582867 is a reply to message #582858] Tue, 23 April 2013 03:04 Go to previous messageGo to next message
Roachcoach
Messages: 1194
Registered: May 2010
Location: UK
Senior Member
mmi78 wrote on Tue, 23 April 2013 08:50
@Roachcoach, i understand after marked kill, then alter system kill session do nothing. But my main concern is in v$session it show killed for a longer time, how to kill those killed session. One way is os kill using kill -9 spid, but when i try to find out spid, i failed. I try to join with v$process for spid, but no row return for those killed session.

I also try to find out active transaction using v$transaction view, but v$transaction has no record.



 select * from v$process where addr=PADDR_FROMV$SESSION
Re: how to kill killed session [message #582868 is a reply to message #582858] Tue, 23 April 2013 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I try to join with v$process for spid, but no row return for those killed session.


Which means your processes have already been killed.
You just have to wait for PMON to rollback the current transactions (which are no more in v$transaction because there are not running sessions).

Check v$lock to see if there are some locks from these sessions, if not then there is no real problem with the killed sessions.

Regards
Michel

Re: how to kill killed session [message #582869 is a reply to message #582859] Tue, 23 April 2013 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nlnkapardi wrote on Tue, 23 April 2013 09:53
mmi

What Is the last_et_call for the killed session can you please paste me the output here.

Regards
kapardi


What does this will give?
What is the rationale behind the question?

Regards
Michel
Re: how to kill killed session [message #582874 is a reply to message #582869] Tue, 23 April 2013 03:22 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
Atlast sessions are killed.

@michel, how many time take to clear session by PMON?
Re: how to kill killed session [message #582875 is a reply to message #582874] Tue, 23 April 2013 03:25 Go to previous messageGo to next message
Roachcoach
Messages: 1194
Registered: May 2010
Location: UK
Senior Member
Depends how much it has to roll back
Re: how to kill killed session [message #582910 is a reply to message #582875] Tue, 23 April 2013 05:52 Go to previous messageGo to next message
Flyby
Messages: 144
Registered: March 2011
Location: Belgium
Senior Member
you also have disconnect session for non-responsive connections
Re: how to kill killed session [message #582914 is a reply to message #582910] Tue, 23 April 2013 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As has been said, the sessions are killed (so no other alter system on them will work) and the processes have also been killed (or disappeared), so there is nothing more than to wait (or restart the instance).

Regards
Michel
Re: how to kill killed session [message #582916 is a reply to message #582910] Tue, 23 April 2013 06:25 Go to previous messageGo to next message
mmi78
Messages: 22
Registered: April 2013
Location: dhaka
Junior Member
so run alter system kill session and alter system disconnect together or alter system disconnect alone?

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;


or

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;


Need to know for future, if the situation arise again...

[Updated on: Tue, 23 April 2013 06:26]

Report message to a moderator

Re: how to kill killed session [message #582919 is a reply to message #582916] Tue, 23 April 2013 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use only ONE of these statements on a session.
You have to choose how you want to terminate it.
Carefully read the documentation about the different options so you can know what is the best one for the case you are (now for this specific case it is too late).

Regards
Michel
Re: how to kill killed session [message #585210 is a reply to message #582844] Thu, 23 May 2013 00:43 Go to previous messageGo to next message
deeplydrink
Messages: 14
Registered: May 2013
Location: Beijing
Junior Member
Hi mimi:

I suggest you kill the session in the OS level because the session killed in db level does not disappeared immediate(the pmon will take care of it later)

So It's better to kill in os level either in winnt or unix platform
Re: how to kill killed session [message #585215 is a reply to message #585210] Thu, 23 May 2013 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it is not better, it is the last recourse when everything at database level failed.

Regards
Michel
Re: how to kill killed session [message #585242 is a reply to message #585215] Thu, 23 May 2013 03:05 Go to previous messageGo to next message
deeplydrink
Messages: 14
Registered: May 2013
Location: Beijing
Junior Member
But if you want a quick reaction,it's better to kill it on os level, because it take time when the session was killed at db level and acctually,the session was not remove at all..

In many situation,especailly a production envrionment,it's better to react quickly
Re: how to kill killed session [message #585244 is a reply to message #585242] Thu, 23 May 2013 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But if you want a quick reaction,it's better to kill it on os level, because it take time when the session was killed at db level and acctually,the session was not remove at all..


No, it is not better and not faster.
In addition, it relies on exception trapping and handling which is always risky than the normal way.
As I said it is (or should be) the last resort.

Regards
Michel
Re: how to kill killed session [message #585245 is a reply to message #585244] Thu, 23 May 2013 03:12 Go to previous messageGo to next message
deeplydrink
Messages: 14
Registered: May 2013
Location: Beijing
Junior Member
Michel Cadot wrote on Thu, 23 May 2013 03:08
Quote:
But if you want a quick reaction,it's better to kill it on os level, because it take time when the session was killed at db level and acctually,the session was not remove at all..


No, it is not better and not faster.
In addition, it relies on exception trapping and handling which is always risky than the normal way.
As I said it is (or should be) the last resort.

Regards
Michel


OK, you win. ^_^
Re: how to kill killed session [message #585246 is a reply to message #582850] Thu, 23 May 2013 03:13 Go to previous messageGo to next message
deeplydrink
Messages: 14
Registered: May 2013
Location: Beijing
Junior Member
mmi78 wrote on Tue, 23 April 2013 02:17
i had also try to killed those killed session using immediate and record still get from v$session. and already 30 mins over but killed sessions still show in v$session. 30 minutes is not enough for clear it?




It really take time..
Re: how to kill killed session [message #585249 is a reply to message #585246] Thu, 23 May 2013 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure, there are cases you have to use "disconnect session" instead of "kill session", yuo have to analyze the situation to know which command to execute.
Disconnect has in the end the effect to kill the Oracle server process but this is Oracle that does it and so it knows what to do before to have a clean state.
Killing at OS level is a behaviour of last century when "disconnect session" didn't exist.
And "disconnect session" was introduced to address the "kill session" issue.

Regards
Michel
icon14.gif  Re: how to kill killed session [message #585310 is a reply to message #585249] Thu, 23 May 2013 12:03 Go to previous message
gkotla
Messages: 1
Registered: May 2013
Location: US - Lexington
Junior Member
Thankyou all. The discussion is very informative.
Previous Topic: ORA-00600: [kql-hash-collision]
Next Topic: Logwr Blocking Other session
Goto Forum:
  


Current Time: Thu Aug 21 19:18:18 CDT 2014

Total time taken to generate the page: 0.11036 seconds