Blocking Session in Oracle

Hey guys, in this blog I am going to explain to you about Blocking Session in Oracle.

Blocking Session-

A blocking session in Oracle Database is one where one session is impeding the progress of another session. When one session has a lock on a resource that another session is attempting to access, this can occur. A session that is blocked is often waiting for a resource that another session is using right now. Performance problems and delays in database operations may result from this. 

Here, I'll explain how to check for blocking sessions and how to end them.

 

Step 1- To Check the Process in Oracle like pmon. 

[oracle@clota-labl08 ~]$ ps -ef | grep -i pmon

oracle      2207       1  0 Sep26 ?        00:00:05 ora_pmon_clotaDB

oracle     43671   37353  0 04:47 pts/4    00:00:00 grep --color=auto -i pmon

 

Step 2 – To Set the Environment for the Oracle

[oracle@clota-labl08 ~]$ . oraenv

ORACLE_SID = [DB1] ? clotaDB

The Oracle base remains unchanged with value /u01/app/oracle

Step 3 – Login as sys account.

[oracle@clota-labl08 ~]$ sqlplus / as sysdba

Step 4- Using a SQL query, identify the stuck session.

 

select sid, serial# ,username, STATUS,LAST_CALL_ET,SQL_ID,MACHINE from v$session where username not in('NULL');

Step 5 – Using this query to find a SQL Text using the SQL ID.

 

select SQL_TEXT,SQL_ID from v$sql where sql_id='gbfr85qxzqs96';

Step 6- Enter some data into the emp1 table and delete the entry by user 1. 

 

Step 7- If another user deletes the same item, it will wait until user 1 commits or rolls back the session. 

Step 8 - Using the SQL query, find the blocking session. It will display the blocking session id, SID, Serial ID, Wait Class, and how long any other query is waiting in seconds. 

 

Select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session where blocking_session is not NULL order by blocking_session; 

Step 9- Finally, the session will then be terminated. 

 

alter system kill session '507,65244' immediate;

Step 10- It will now show you that your session has been terminated by another user.