Alter System Kill Session, Disconnect Session

Kill Session vs Disconnect Session

Oracle SQL provides end_session_clauses syntax to terminate sessions:

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

1. ALTER SYSTEM KILL SESSION

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

Using GUI tools like Toad for Oracle or SQL developer to monitor and kill sessions is also a convenient way to do it.

2. ALTER SYSTEM DISCONNECT SESSION

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

"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

"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.

We specified SID and SERIAL# as input arguments in the statement.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

For example, we can select SID and SERIAL# from V$SESSION by identifying their 3 unique features, i.e. USERNAME, MACHINE and PROGRAM.

SQL> select sid, serial# from v$session where username = 'ERPAPP' and lower(machine) = lower('WORKGROUP\WIN-PH3N74QAP18') and lower(program) like lower('%plsqldev%');

       SID    SERIAL#
---------- ----------
        14      28921
       131      16726
       257      38783

We leverage the above statement to compose killing statements like this:

SQL> select 'ALTER SYSTEM KILL SESSION ''' || sid || ', ' || serial# || ''';' stmt from v$session where username = 'ERPAPP' and lower(machine) = lower('WORKGROUP\WIN-PH3N74QAP18') and lower(program) like lower('%plsqldev%');

STMT
-----------------------------------------------
ALTER SYSTEM KILL SESSION '14, 28921';
ALTER SYSTEM KILL SESSION '131, 16726';
ALTER SYSTEM KILL SESSION '257, 38783';

Then issue any of the statements that you want to kill.

2. Normally kill a session with specifying instance ID.

We additionally use INST_ID to specify which instance should be applied.

ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INST_ID';

In a RAC database system, you need to specify @INST_ID to kill a session from any other instance, but for local sessions, you can ignore it.

To compose killing statements in a RAC database, we should query GV$SESSION instead of V$SESSION view.

SQL> select 'ALTER SYSTEM KILL SESSION ''' || sid || ', ' || serial# || ', @' || inst_id || ''';' stmt from gv$session where username = 'ERPAPP' and lower(machine) = lower('WORKGROUP\WIN-PH3N74QAP18') and lower(program) like lower('%sqlplus%');

STMT
-----------------------------------------------
ALTER SYSTEM KILL SESSION '1221, 4334, @2';
ALTER SYSTEM KILL SESSION '1821, 8545, @2';
ALTER SYSTEM KILL SESSION '3326, 54404, @2';

Then issue any of the statements that you want to kill.

3. Immediately kill a session in current instance.
ALTER SYSTEM KILL SESSION 'SID, SERIAL#' IMMEDIATE;
4. Immediately kill a session with specifying instance ID.
ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INST_ID' IMMEDIATE;
5. Normally kill a session in current instance, and don't recover it under Application Continuity (AC).
ALTER SYSTEM KILL SESSION 'SID, SERIAL#' NOREPLAY;
6. Normally kill a session with specifying instance ID, and don't recover it under Application Continuity (AC).
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.
ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' POST_TRANSACTION;
2. Immediately kill the server process or the virtual circuit of this session in current server.
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.