How To Kill Session In Oracle 11g Database With New Feature?

In various critical situations Oracle DBA has to decide to kill existing session or kill the background process of Oracle 11g database. At this moment remote Oracle DBA should need to terminate session/process called as murder of session of any instance.   From Oracle 11g database there is new feature introduced to kill session from sqlplus. Alter system disconnect session command is introduced in Oracle 11g database with some extra new scope.

New feature in Oracle 11g database to kill session:

Using SQL*Plus (alter system with disconnect session command): Now we can kill session after finishing existing transaction to be finished. Means in previous release there was no scope to finish on going transaction. Instead of “alter system kill session” here we can use “alter system disconnect session”. Using syntax “POST_TRANSACTION” we can kill session for active transaction has to be completed first and after that it will be automatically killed. Example of same syntax is given below.

SQL> alter system disconnect session ‘9,171’ post_transaction;

System altered.

Session can be kill without finishing active transaction using “IMMEDIATE” syntax with “alter system disconnect session” command. This command kills and terminate existing session immediately and roll back if any active transaction. This syntax is same as previous command “alter system kill session” with immediate syntax as given following example.

SQL> alter system disconnect session ‘9,171’ immediate;

System altered.

But when we are executing command “alter system disconnect session” without above both clauses (means POST_TRANSACTION & IMMEDIATE), error is being generated called ORA- 02000: missing POST_TRANSACTION or IMMEDIATE keyword.

SQL>alter system disconnect session ‘9,171’;

ORA- 02000: missing POST_TRANSACTION or IMMEDIATE

Means we can’t execute “alter system disconnect session” without specific keywords like post_transaction or immediate. In contrast we can execute “alter system kill session” syntax without any specific more syntax like immediate in prior release of Oracle 11g database.   Actually this feature “alter system disconnect session” was introduced in Oracle 10g. In Oracle 10g database this feature and syntax was as new feature of Oracle 10g database.