Learn Database Concepts

Note : Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed.   There could be a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows us to kill these Oracle sessions.  The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number. First you have to identify the session to be killed with alter system kill session. select SID,SERIAL#,STATUS,SCHEMANAME,PROGRAM from v$session; The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued. SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'; Sometimes Oracle is not able to kill the session immediately with the alter system kill session  command alone. Upon issuing the alter system kill session  command, the session will b...

If you have configured IDLE_TIME inr your user profile. IDLE_TIME  Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit Lets say a session has been idle for 10 Minutes. Session will continue to show as idle even after the idle_time for that user,as specified in that user's profile, has expired. When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. After this, the session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session. Reference this oracle Document for more information. Once the oracle session is changed to SNIPED status, we can kill that se...