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.
50317 rows updated.
SQL> select min(sid) from v$mystat;
MIN(SID)
———-
136
MIN(SID)
———-
136
Session 2:
SQL> select username, sid, serial# from v$session where sid=136;
USERNAME SID SERIAL#
—————————— ———- ———-
NIRADJ 136 56
USERNAME SID SERIAL#
—————————— ———- ———-
NIRADJ 136 56
SQL> alter system disconnect session ‘136,56’ post_transaction;
System altered.
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
MIN(SID)
———-
136
SQL> rollback;
Rollback complete.
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
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.
50317 rows updated.
SQL> select min(sid) from v$mystat;
MIN(SID)
———-
137
Session 2:
MIN(SID)
———-
137
Session 2:
SQL> select username, sid, serial# from v$session where sid=137;
USERNAME SID SERIAL#
—————————— ———- ———-
NIRADJ 137 92
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:
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
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.
