How to kill session in Oracle 11g database with new feature?

In some 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 database administrator should need to terminate session/process called as terminate of session of any instance. Dbametrix explains resolution of this issue.While you are providing remote dba services and you require to kill session then you need to perform this activity using command line only.

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. As expert remote dba you should be aware of this new feature.

Using SQL*Plus (alter system with disconnect session command):

New feature in Oracle 11g database to kill session:

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.

(Remember) 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

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

Dbametrix is expert remote dba service provider team. Dbametrix has solid understanding to make SLA as per specification and requirement of client and end users. Dbametrix believes to provide remote dba services of database administration using SLA. Due to this reason client of Dbametrix can able to put trust on company because Dbametrix delivers cost effective remote dba plan using Service Level Agreement SLA and response time matrix.

Dbametrix is world wide leader in remote dba support. Expert remote DBA team of Dbametrix is offering high quality professional Oracle DBA support with strong response time to fulfill your SLA. Contact our sales department for more information.

Check another articles on same topic of different execution type.
How to kill session in Oracle RAC database::
How to kill session in Oracle using SQL*Plus::
How to kill session in Oracle using operating system commands::