Blocking Sessions in oracle database

Blocking Sessions:

Applies to: Oracle 9i/10g/11g

Blocking session occurs when one session acquired an exclusive lock on an object and doesn't release it,  another session (one or more) want to modify the same data.First session will block the second until it completes its job

Finding blocking sessions:

Using v$session:

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL

Using v$lock:

select * from v$lock where block=1;select count(*) from gv$lock where block=1;select sid from v$lock where block=1;

Which Session is blocking?

select

(select username from v$session where sid=a.sid) blocker,

a.sid,' is blocking ',(select username from v$session where sid=b.sid) blockee,b

.sid

from v$lock a, v$lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

Finding the query of the sessions:

SELECT a.sql_text, b.sql_hash_value

FROM   v$sqltext a,

       v$session b

WHERE  a.address = b.sql_address

AND    a.hash_value = b.sql_hash_value

AND    b.sid = &1

ORDER BY a.piece;

Complete Details of blocking sessions:

select distinct

a.sid "waiting sid"

, d.sql_text "waiting SQL"

, a.ROW_WAIT_OBJ# "locked object"

, a.BLOCKING_SESSION "blocking sid"

, c.sql_text "SQL from blocking session"

from v$session a, v$active_session_history b, v$sql c, v$sql d

where a.event='enq: TX - row lock contention'

and a.sql_id=d.sql_id

and a.blocking_session=b.session_id

and c.sql_id=b.sql_id

and b.CURRENT_OBJ#=a.ROW_WAIT_OBJ#

and b.CURRENT_FILE#= a.ROW_WAIT_FILE#

and b.CURRENT_BLOCK#= a.ROW_WAIT_BLOCK#

Find the Unix process id from SID:

select spid 

from  v$process 

where background is null 

and     addr in (select paddr

                        from   v$session

                        where  sid=&session_id);

Find SID from SPID: (Not very much required here)

select s.username, s.status,  s.sid,     s.serial#,

      p.spid,     s.machine, s.process, s.lockwait

from   v$session s, v$process p

where  s.process  = '&unix_pid'

and    s.paddr    = p.addr;

Blocking Session has to be released by taking concurrence with application team:

How to release a blocking Session:

Ge the SID details:

select sid,SERIAL#,status,username from v$session where sid=<Blocking Session>;

select SID,MACHINE,TERMINAL,PROGRAM,MODULE from v$session where sid=<Blocking Session>;

Disconnecting the Session:

alter system disconnect session '<SID>,<Serial#>' IMMEDIATE;

Kill the Server Process:

kill -9 <Unix Process Id from SID>

Corrective Action:

For a blocking session only two corrective actions:

    1. Disconnect the blocking session

    2. Wait for completing the blocking session

Preventive Action:

Application has to be designed/corrected as no two or more sessions required the same data at the same time to be modified.