Customer Portal

How to kill session in Oracle using command in sqlplus?

Article

Kill Oracle process

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. At this moment remote Oracle DBA should need to terminate session/process called as murder of session.


Which situation kill session or process needed:

Then question is raised that in under which type of situation remote dba should need to kill session and process. We are explaining those situations and incidences of database.Some critical situation Oracle DBA should need to take decision to terminate session.


Kill session needed under following typical and critical circumstances:

kill session oracle

Kill Session: Terminate process/session in Oracle using SQLPLUS command. View large

When blocking lock occurs and other sessions are waiting to acquire a lock on same object. But blocker session doesn't end the transaction. At this moment Remote oracle dba should need to identify blocking session and terminate it for clearing resources for other sessions.


When maximum connections reached error occurs. No room available for any new connection or session. At this time Remote Oracle DBA should need to kill some of idle processes from Oracle database.


When database found in hang status and shutdown abort command also doesn't work, at same time Remote Dba should need to terminate instance abnormally with murdering background processes. In above critical situation we need to kill or terminate or murder some session or process using command line or GUI utility like OEM, TOAD etc. But command line is more easy and helpful to solve such critical situation in remote DBA support services.


How to kill session using command line of Oracle:


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

It is very simple way to kill session using SQL command. Just check serial no and sid (system identifier) from v$session view as follows and use "alter system kill session" with sid and serial# command.


SQL>select sid,serial# from v$session where machine='GPTWORKGROUP';
SID SERIAL#
--------- ---------
9 171
1 row selected
SQL> alter system kill session '9,171';
System altered
OR
SQL>alter system kill session '9,171' immediate;
System altered.

And terminated user session will get message in sqlplus that "your session has been killed". We can get output of status as "killed" from v$session for terminated session.


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 using Oracle 11g new feature::
How to kill session in Oracle RAC database::
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