Skip to content

KILL SESSION vs DISCONNECT SESSION

  • by

Oracle SQL provides end_session_clauses syntax to terminate sessions:

ALTER SYSTEM { DISCONNECT SESSION 'integer1, integer2' [ POST_TRANSACTION ] | KILL SESSION 'integer1, integer2 [,@integer3]' } [ IMMEDIATE | NOREPLAY ]

In which, two type of sub-clauses can do this job but with subtle differences. Let's see how Oracle defines these clauses:

  • KILL SESSION by Oracle
  • The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources.

  • DISCONNECT SESSION by Oracle
  • 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).

Under such definitions, DISCONNECT seems more aggressive and intends to remove the sessions from the ground.

Before we see how to use the two clauses under various scenarios. I should let you know that I use two different terms in this post to distinguish different behaviors:

  • "Normally" in this post
  • "Normally" means PMON will mark the session to be terminated until ongoing transactions are all closed (i.e. COMMIT or ROLLBACK). In other words, it's just like to terminate the session with WAIT.

  • "Immediately" in this post
  • "Immediately" means PMON will rollback ongoing transactions, release locks and recover all related states immediately. In other words, it's just like to terminates the session with NOWAIT.

Now, let's see the usage of ALTER SYSTEM KILL SESSION and ALTER SYSTEM DISCONNECT SESSION.

ALTER SYSTEM KILL SESSION

  1. Normally kill a session in current instance.
  2. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL';
  3. Normally kill a session with specifying instance ID.
  4. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL, @INST_ID';
  5. Immediately kill a session in current instance.
  6. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL' IMMEDIATE;
  7. Immediately kill a session with specifying instance ID.
  8. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL, @INST_ID' IMMEDIATE;
  9. Normally kill a session in current instance, and don't recover it under Application Continuity (AC).
  10. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL' NOREPLAY;
  11. Normally kill a session with specifying instance ID, and don't recover it under Application Continuity (AC).
  12. SQL> ALTER SYSTEM KILL SESSION 'SID, #SERIAL, @INST_ID' NOREPLAY;

ALTER SYSTEM DISCONNECT SESSION

  1. Normally kill the server process or the virtual circuit of this session in current server.
  2. SQL> ALTER SYSTEM DISCONNECT SESSION 'SID, #SERIAL' POST_TRANSACTION;
  3. Immediately kill the server process or the virtual circuit of this session in current server.
  4. SQL> ALTER SYSTEM DISCONNECT SESSION 'SID, #SERIAL' IMMEDIATE;

Please note that, you must use either POST_TRANSACTION or IMMEDIATE as a complement in DISCONNECT SESSION clause, otherwise, the statement will fail.

Leave a Reply

Your email address will not be published.