When an inactive session has been terminated, STATUS in the V$SESSION view is KILLED. Please note that, you must use either POST_TRANSACTION or IMMEDIATE as a complement in DISCONNECT SESSION clause, otherwise, the statement will fail.This is what Oracle explaination for killed sessions not being removed from v$session view and holding the session process and getting ORA-00020 Max processes exceeded error (when you have limited processes that you can configure on the server). SQL> ALTER SYSTEM DISCONNECT SESSION 'SID, #SERIAL' IMMEDIATE SQL> ALTER SYSTEM DISCONNECT SESSION 'SID, #SERIAL' POST_TRANSACTION Immediately kill the server process or the virtual circuit of this session in current server. ALTER SYSTEM DISCONNECT SESSION Normally kill the server process or the virtual circuit of this session in current server. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL, NOREPLAY 2. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL' NOREPLAY Normally kill a session with specifying instance ID, and don't recover it under Application Continuity (AC). SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL, IMMEDIATE Normally kill a session in current instance, and don't recover it under Application Continuity (AC). SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL' IMMEDIATE Immediately kill a session with specifying instance ID.
SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL, Immediately kill a session in current instance.
SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL' Normally kill a session with specifying instance ID. ALTER SYSTEM KILL SESSION Normally kill a session in current instance. Now, let's see the usage of ALTER SYSTEM KILL SESSION and ALTER SYSTEM DISCONNECT SESSION. In other words, it's just like to terminates the session with NOWAIT. "Immediately" means PMON will rollback ongoing transactions, release locks and recover all related states immediately. In other words, it's just like to terminate the session with WAIT. "Normally" means PMON will mark the session to be terminated until ongoing transactions are all closed (i.e. I should let you know that I use two different terms in this post to distinguish different behaviors: Under such definitions, DISCONNECT seems more aggressive and intends to remove the sessions from the ground.īefore we see how to use the two clauses under various scenarios. Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever). The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. Let's see how Oracle defines these clauses: In which, two type of sub-clauses can do this job but with subtle differences.
0 kommentar(er)
