Customer Portal

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


Kill Oracle 11g session

Most Popular
Tips and technical articles of Oracle DBA
2009-2010   |  Oracle DBA Tips  |  Author: Gitesh Trivedi

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 terminate of session of any instance. Dbametrix explains resolution of this issue.

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.

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.

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';


oracle 11g rac new features

Kill Session: Kill session using new feature of Oracle 11g database. View large

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 services of database administration using SLA. Due to this reason Dbametrix offers SLA based cost effective remote dba plans. 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.

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::
Gitesh Trivedi
- TL
Back to DBA Tips Articles
Copyright Dbametrix Solutions © 2005. All Rights Reserved.
Back to page top