- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to identify blocked and blocking sessions in Oracle ?
You’d like to identify the blocking and the blocked sessions in your database.
When we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that is held by a different session. We can issue the following command to view information about the blocked and the blocking sessions in ORacle.
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;
The V$LOCK view shows if there are any blocking locks in the instance. If there are blocking locks, it also shows the blocking session(s) and the blocked session(s).
A blocking session can block multiple sessions simultaneously, if all of them are wanting to use the same object that is being blocked.
You can use below SQL to fetch the information.
select sid,type,lmode,request,ctime,block from v$lock;
SID TY LMODE REQUEST CTIME BLOCK -------------- -------- ----------- ----------- -------- ------- 140 TX 4 6 11655 0 38 TM 3 0 826 0 38 TX 6 0 826 1
The key column to watch is the BLOCK column which will have the value 1 for the blocking session. In our example, session 38 is the blocking session, because it shows the value 1 under the BLOCK column. The blocking session, with a SID of 38, also shows a lock mode 6 under the LMODE column which mean it is holding this lock in the exclusive mode. Therefore session 140 is hanging for the same reson and unable to perform its update operation. The blocked session shows a value of 0 in the BLOCK column.
If you want to find out the wait class and for how long a blocking session has been blocking others, we can do so by querying the V$SESSION view.
SELECT blocking_session, sid, wait_class, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL ORDER BY blocking_session;
BLOCKING_SESSION SID WAIT_CLASS SECONDS_IN_WAIT ----------------- -------- ------------- ------------------- 38 140 Application 1237
- How to identify recent WAIT events in a Oracle database ?
- How to identify the SQL consuming more resources in Oracle?
- How to identify SQL queries with the most waits in Oracle?
- What is Blocking Networks and Non-Blocking Networks in Computer Architecture?
- How to use Sessions in Postman?
- How to create sessions in Postman?
- How to identify rows that are not parents of any other rows in a Hierarchy table in Oracle?
- How to Identify and Prevent a Smartphone Scam?
- Blocked Bloom Filter
- Difference between oracle golden gate and oracle active guard in the oracle
- How to find and replace text in Oracle?
- How to insert and retrieve dates in Oracle ?
- Are jQuery events blocking?
- What is ad blocking?
- Difference between Oracle 11g and Oracle 12c