ALTER SYTEM DISCONNECT vs ALTER SYSTEM KILL for currently connected sessions

source : https://oracletempspace.wordpress.com/2011/04/20/example-alter-sytem-disconnect-vs-alter-system-kill-for-currently-connected-sessions/

Every once in a while, I come across a clause for a commonly-used SQL statement/command that I’ve lost touch with. Recently this happened to me while looking up some SQL commands regarding the ‘ALTER SYSTEM’ options in Oracle.

Normally, when we are required to end a user session manually, we turn to the ALTER SYSTEM KILL SESSION ‘,'’ command (with additional options as required). However, another potential choice in these situations is the DISCONNECT clause, which causes a session to be disconnected from the Oracle database after they have completed the current transaction (in effect, after they have committed or rolled back the current transactions in their session).         

A simple example to contrast this difference is provided below:

Session 1 (a simple update of an existing table, T1):

SQL> update t2 set object_id=object_id+1;
50317 rows updated.

SQL> select  min(sid) from v$mystat;
  MIN(SID)
  ———-
       136

Session 2:

SQL> select username, sid, serial# from v$session where sid=136;
USERNAME                              SID    SERIAL#
—————————— ———- ———-
NIRADJ                                136         56

SQL> alter system disconnect session ‘136,56’ post_transaction;
System altered.

Back in the first session, we would see the following when we issue a ROLLBACK or COMMIT:

SQL> select  min(sid) from v$mystat;
  MIN(SID)
  ———-
       136

SQL> rollback;
Rollback complete.

SQL> select  min(sid) from v$mystat;
select  min(sid) from v$mystat
*
ERROR at line 1:
ORA-00028: your session has been killed

Now, contrast this with a typical ALTER SYSTEM KILL SESSION:

Session 1:

SQL> update t2 set object_id=object_id+1;
50317 rows updated.

SQL> select  min(sid) from v$mystat;
  MIN(SID)
  ———-
     137
Session 2:

SQL> select username, sid, serial# from v$session where sid=137;
USERNAME                              SID    SERIAL#
—————————— ———- ———-
NIRADJ                                137         92

SQL> alter system kill session ‘137,92’;
System altered.
Back in the first session we would see the following:

SQL> select  min(sid) from v$mystat;
select  min(sid) from v$mystat
*
ERROR at line 1:
ORA-00028: your session has been killed

So here even though the current session has an active transaction, we see that the database has directly disconnected them, without the current transaction being finalized (either committed or rolled back) by the user. Instead, the database has automatically rolled back everything that was uncommitted in the background.