Why and how to check/kill connected sessions in your Oracle DB

  Why ?

All users/Application are connected to the DB server if it is dedicated session configured therefore you will find theses connected as a session in session views in your DB and as PID (process) in the OS of server, any query run to do DDL or DML in the DB can be stopped by killing it is connected session, Also, prior any shutdown with option immediate or abort you may check these affected session or you may shutdown with transactional option and kill these connected session manually .

How ?

 To check connected session in RAC because it is more than one node always use gv$ opation instead of v$ in all views , here I am using gv$ option for both RAC and none RAC  :

1- check connected session :

set linesize 300

col username format A10

col program format A50

col osuser format A10

select b.INST_ID,

       substr(a.spid,1,9) pid,

       substr(b.sid,1,5) sid,

       substr(b.serial#,1,5) ser#,

       substr(b.username,1,10) username,

--       b.server,

       substr(b.osuser,1,8) os_user,

       substr(b.program,1,30) program

from gv$session b, gv$process a

where

b.paddr = a.addr

and type='USER'

order by spid;

2- To kill connected session use  :

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;