Blocking Sessions and How to Kill them

 

 ALTER SYSTEM KILL SESSION

==============================================================

Blocking sessions occur when a session issues an "insert, update or delete" command that changes a row. When the change occurs,"the row is locked until the session either commits the change, rolls the change back or the user logs off the system." 

    select

blocking_session,

sid,

serial#,

wait_class,

seconds_in_wait

    from

        v$session

    where

blocking_session is not NULL

    order by

blocking_session;

=========================================================================

How to check Locking session/Blocking session

 

col username for a15

 col machine for a20

 col event for a20

 col program for a20

 col osuser for a20

 set line 3000

 set pagesize 2000

 SELECT

 Inst_id,

    s.username,

    s.blocking_session blk_sess,

    s.blocking_instance blk_inst,

    s.sid, 

    s.serial#, 

    s.sql_id,

    s.status,

    s.seconds_in_wait waittime,

    s.event,

    s.machine 

 FROM

    gv$session s

 WHERE

    blocking_session IS NOT NULL order by SECONDS_IN_WAIT;

=========================================================================

  How to search users on database 

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

################################################################################

To get spid and then kill them 

$ ps -ef | grep ora 

$ kill -9 spid 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';   -------------------------> single instance db

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';  ------------------> RAC instance db

###################################################################################

for a single instance database : 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

============================================================

In a RAC environment, you optionally specify the INST_ID, 
shown when querying the GV$SESSION view.This allows you 
to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

=========================================================================

The KILL SESSION command doesn't actually kill the session. 

It merely asks the session to kill itself. 

In some situations, like waiting for a reply from a remote database or rolling

back transactions, the session will not kill itself immediately and will wait 

for the current operation to complete. In these cases the session will have a 

status of "marked for kill". 

It will then be killed as soon as possible.

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

=========================================================================

ALTER SYSTEM DISCONNECT SESSION
POST_TRANSACTION >> DISCONNECT AFTER THE TRANSACTION COMPLETES
IMMEDIATE >> DISCONNECT SUDDENLY

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

=========================================================================

ALTER SYSTEM CANCEL SQL (18c+)
to cancel a SQL statement in a session, providing an alternative to killing a rogue session.

SQL> ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';

=========================================================================

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

SET LINESIZE 100

COLUMN spid FORMAT A10

COLUMN username FORMAT A10

COLUMN program FORMAT A45

SELECT s.inst_id,

       s.sid,

       s.serial#,

       --s.sql_id,

       p.spid,

       s.username,

       s.program

FROM   gv$session s

       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != 'BACKGROUND';

#############################################################################

col inst_id for a13

col sid for a12

col serial# for a12

col username for a14

col status for a12

col server for a14

col schemaname for a12

col osuser for a14

col machine for a13

col program for a13

select inst_id,sid,serial#,username,status,server,schemaname,osuser,machine,program,sql_id from gv$session

where status = 'ACTIVE';

##################################################################################

Working status and seconds v$session 

col "Description" format a50

select sid,

        decode(state, 'WAITING','Waiting',

                'Working') state,

        decode(state,

                'WAITING',

                'So far '||seconds_in_wait,

                'Last waited '||

                wait_time/100)||

        ' secs for '||event

        "Description"

from v$session

where username = 'username';

###################################################################################

select SID, osuser, machine, terminal, service_name,

       logon_time, last_call_et

from v$session

where username = 'userename';

###################################################################################

Session wait for each machine 

-------------------------------------------

col username format a5

col program format a10

col state format a10

col last_call_et head 'Called|secs ago' format 999999

col seconds_in_wait head 'Waiting|for secs' format 999999

col event format a50

select sid, username, program,

        decode(state, 'WAITING', 'Waiting',

                'Working') state,

last_call_et, seconds_in_wait, event

from v$session

where machine = 'hostname';

###################################################################################