How to find Oracle blocking sessions details

Hey DBA-Mates,
Here, we are going to explore about the oracle blocking session.

Oracle Blocking Session:
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.

Query For Blocking sessions:
select sid "Session id ", decode(Block,1, 'Blocking Session','Waiting Session') Details, ctime  "Blocking Time /Waiting time",id2 from v$lock where (block <> 0 or request <>0 ) and ctime > 200 order by id2,Details;

OR

SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait

FROM v$session

WHERE blocking_session is not NULL order by blocking_session;

OR

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)   ORDER BY id1, request; 

Query For Locking Session:
To get all the details of the locks held and session waiting for them and time of the lock, this script is very important to see if there any blocking sessions:

col username  for a10
col held for a12
col requested for a12

select sid, type "LOCK TYPE", id1, id2,
decode(lmode ,
1 , 'NULL',
2 , 'Row-S(SS)',
3 , 'Row-X(SX)',
4 , 'Share(S)',
5 , 'S/Row-X(SSX)',
6 , 'Exclusive(X)' ) HELD,
decode(request,
1 , 'NULL',
2 , 'Row-S(SS)',
3 , 'Row-X(SX)',
4 , 'Share(S)',
5 , 'S/Row-X(SSX)',
6 , 'Exclusive(X)' ) REQUESTED,
ctime "TIME(SEC)"
from v$lock
where id1 in
(select id1 from v$lock where request != 0 )
order by 2,3,5

/

Here for Normal case, you can kill the session by below query:

SQL>alter system kill session 'SID,SERIAL#';

But, the Question is how we will find the Blocking session in RAC environment.

To find out the blocking sessions in RAC, we can query gv$lock and gv$session views. But we can’t kill a session here in RAC as same as an ordinary blocking session.

Please find the below query to get the session details.

SQL> select inst_id,sid,serial# from gv$session where username='ORA-DATA';

   INST_ID        SID    SERIAL# 
---------- ---------- ---------- 
         4        119        519

SQL>  alter system kill session '119,519,4'; 
 alter system kill session '119,519,4' 
* 
ERROR at line 1: 
ORA-00026: missing or invalid session ID

But now this will works:

ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @inst_id';

SQL> alter system kill session '119, 519, @4’;

System altered.

SQL>

OR another way also you can use following command to kill a session in Oracle RAC without waiting for confirmation to kill.
 

SQL>alter system kill session '119,519,@4' immediate;

System altered. 
SQL>

Hope these below links may useful:

Crontab Concepts

R12.1 Vs R12.2

Jobs Tips

Ora-609

Interviews Questions

Hope the above information will be useful and helpful. Please let us know for any suggestions or comments either by comments box or write to us @contact us https://ora-data.blogspot.in/

Regards,
ora-data Team