Disconnect Session - 10g

SQL * Plus now enables killing of sessions through the usage of “alter system disconnect session” command with mandatory usage of “post_transaction” or “immediate” options. Using “post_transaction” with “alter system disconnect session” results in killing of the session after execution of the active transaction whereas “immediate” option terminates the session without any delay. In prior versions of Oracle it was the syntax "alter system kill session" that killed sessions immediately making it synonymous with the syntax “alter system disconnect session” with “immediate” option. But it is with the “post_transaction” option that “alter system disconnect session” scores over "alter system kill session". 

In Session-1 initiated some transaction. While Session-1 is in mid of executing transaction, session-2 tries to Kill Session-1.

12:04:09 SESSION-1>
12:04:10 SESSION-1> create table t (x number);

Table created.

Elapsed: 00:00:00.00
12:04:13 SESSION-1>
12:04:14 SESSION-1> create or replace procedure p(p_limit in number)
12:04:18   2  as
12:04:18   3  begin
12:04:18   4    for i in 1..p_limit
12:04:18   5    loop
12:04:18   6            insert into t values(i);
12:04:18   7            dbms_lock.sleep(i);
12:04:18   8    end loop;
12:04:18   9    commit;
12:04:18  10  end p;
12:04:18  11  /

Procedure created.

Elapsed: 00:00:00.01
12:04:19 SESSION-1>
12:04:21 SESSION-1> select userenv('sid') from dual;

USERENV('SID')
--------------
            15

Elapsed: 00:00:00.01
12:04:31 SESSION-1>
12:04:31 SESSION-1>
12:05:46 SESSION-1>
12:05:47 SESSION-1> select * from t;

no rows selected

Elapsed: 00:00:00.00
12:05:50 SESSION-1>
12:05:51 SESSION-1> exec p(10);

PL/SQL procedure successfully completed.

Now Session-2 kills Session-1 using Alter system POST_TRANSACTION command. This post_transaction command setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.

SESSION-2> alter system disconnect session '15,313' post_transaction;

System altered.

Elapsed: 00:00:00.03
SESSION-2>

Back to Session-1 if we try to retrieve data will return ORA-03114: not connected to ORACLE. since Session-1 is killed by Session-2 but Session-1 ongoing transaction will be completed.

12:06:50 SESSION-1> select * from t;
ERROR:
ORA-03114: not connected to ORACLE


ERROR:
ORA-03114: not connected to ORACLE


Elapsed: 00:00:00.01
12:07:05 SESSION-1>
12:07:05 SESSION-1> connect rajesh@orcl
Enter password:
12:07:28 SESSION-1>
12:07:29 SESSION-1> select * from t;

         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Elapsed: 00:00:00.04
12:07:36 SESSION-1>