How to kill session in Oracle RAC database?

by / 2009-2010 / Published in Oracle DBA Tips

kill session

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. While you are providing remote dba services and you require to kill session then you need to perform this task using command line only.

Some basic syntax is changed for killing session in Oracle RAC instance. Oracle RAC database contains one or more instances. First oracle RAC DBA should need to identify which session has be killed and in which instance contains said target session.

It is very simple way to kill session using command line of Oracle RAC instance:

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

We use v$session for indetifying target session to kill in non-rac database.Here in Oracle RAC instance we should need to check and identify session using gv$session. Because only gv$session contains rac instance identifier column. After identifying target session from gv$session we need command "alter system kill session" with session identifier (sid), serial number (serial#) and instance number of Oracle RAC (instance_no). Here is example to kill or terminate active session from Oracle RAC database or instance.

SQL>select sid,serial#,inst_id from gv$session where machine='GPTWORKGROUP';
--------- --------- -------
9 171 1
1 row selected
SQL> alter system kill session '9,171,@1';
System altered.
OR optionally you can use following command to kill session in Oracle RAC...
SQL>alter system kill session '9,171,@1' immediate;
System altered.

Oracle DBA Interview Questions

And terminated user session will get message in sqlplus that "your session has been killed" in Oracle RAC instance. We can get output of status as "killed" from gv$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 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 using Oracle 11g new feature::
How to kill session in Oracle using SQL*Plus:
How to kill session in Oracle using operating system commands::