Home » RDBMS Server » Server Administration » cleanup killed sessions (oracle 9.2 on solaris 9)
cleanup killed sessions [message #315804] Tue, 22 April 2008 16:52 Go to next message
ORA-MAN
Messages: 24
Registered: January 2008
Location: ===
Junior Member
Hi all,

After the session was killed it still show on v$session with killed status, so what is the way to cleanup it finally?

regards

Report message to a moderator

Re: cleanup killed sessions [message #315811 is a reply to message #315804] Tue, 22 April 2008 17:30 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
The session you killed may still be connected and/or is performing a rollback.

Report message to a moderator

Re: cleanup killed sessions [message #315835 is a reply to message #315804] Tue, 22 April 2008 23:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68676
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle waits the client tries to make an action to tell it it was killed before cleaning the entry.

Regards
Michel

Report message to a moderator

Re: cleanup killed sessions [message #315923 is a reply to message #315804] Wed, 23 April 2008 04:33 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Unix OS command will helps to kill session instantly.
kill -9 Spid

Report message to a moderator

Re: cleanup killed sessions [message #315935 is a reply to message #315923] Wed, 23 April 2008 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68676
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Unix OS command will helps to kill session instantly.

This is wrong it is not instantaneous.
In addtion, "kill -9" may have side effects, it is better to use "kill -15".
In the end, it is better to use "alter system disconnect session" to cleanly do it.

BUT "kill session" rolls back the transaction and releases locks when "disconnect session" (or OS kill process) won't.

Regards
Michel

Report message to a moderator

Re: cleanup killed sessions [message #315943 is a reply to message #315935] Wed, 23 April 2008 05:26 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please tell what is difference between kill -9 and kill -15.

I am only aware of kill -9.

Thanks in advance.

Report message to a moderator

Re: cleanup killed sessions [message #315946 is a reply to message #315943] Wed, 23 April 2008 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68676
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"kill -15" (SIGTERM) is a soft kill, it let the application to do some work, for instance cleaning, before ending.
"kill -9" (SIGKILL) is a force kill, it immediatly terminate the process whatever it is doing.

http://en.wikipedia.org/wiki/SIGTERM

Regards
Michel

Report message to a moderator

Re: cleanup killed sessions [message #315947 is a reply to message #315946] Wed, 23 April 2008 05:36 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Nice reply.
Thanks

Report message to a moderator

Re: cleanup killed sessions [message #316590 is a reply to message #315804] Sat, 26 April 2008 01:12 Go to previous messageGo to next message
ORA-MAN
Messages: 24
Registered: January 2008
Location: ===
Junior Member
Thanks all.

I created a profile then assign to user, this profile has
idle_time&connect_time (1min), when the session takes more than 1min its status changed from INACTIVE to SNIPED and stays for days then cleared.

also I need to alter this profile and add SESSIONS_PER_USER 10, but it will count all SNIPED sessions as connected and it will not allow new connection.

I am searching for a way to clear this SNIPED auto immediately because they are many sessions and dynamic.


Regards

Report message to a moderator

Re: cleanup killed sessions [message #316592 is a reply to message #316590] Sat, 26 April 2008 01:31 Go to previous message
Michel Cadot
Messages: 68676
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am searching for a way to clear this SNIPED auto immediately

Same answers.
Don't setup a max sessions number in this case or ask your client to cleanly disconnect.

By the way, Oracle is not made for sessions of 1 minute. Creating a session is a heavy process not a light one. You should investigate n-tiers environment and connection pooling.

Regards
Michel

[Updated on: Sat, 26 April 2008 01:35]

Report message to a moderator

Previous Topic: Oracle RDBMS 10.2.0.4
Next Topic: Migrate from XE to Standard Edition
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 09 17:58:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.