Posted in SQL

Get the blocking sessions

select s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    from v$lock l1, v$session s1, v$lock l2, v$session s2
    where s1.sid=l1.sid and s2.sid=l2.sid
    and l1.BLOCK=1 and l2.request > 0
    and l1.id1 = l2.id1
    and l2.id2 = l2.id2 ;

SELECT sid, lmode acquired, request, blocking_session, SQL_TEXT 
FROM v$lock l JOIN v$session s USING (sid) LEFT JOIN v$sqlarea USING (sql_id) WHERE block = 1 OR request > 0 ;

Tree like:

WITH sessions_info
AS
(SELECT sid, blocking_session, row_wait_obj#, sql_id FROM v$session)
SELECT DECODE(LEVEL, 1, 'Root Blocker') STATUS, LPAD (' ', LEVEL) || sid sid,
object_name,
SUBSTR (sql_text, 1, 50) sql_text
FROM sessions_info s
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions_info)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
START WITH blocking_session IS NULL;
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
/
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
select * from dba_waiters;
select * from dba_blockers;

If the DBA_WAITERS and DBA_BLOCKERS doesn’t exist, you can execute the script below and create them:

@?/rdbms/admin/catblock.sql
Advertisements

Author:

I am an Oracle DBA with experience in Maximum Availability technology such as RAC, Data Guard, Grid Control and ASM. I worked with version 9i through 12c on a number of operating systems. Next to that I have an extensive experience with PL/SQL, Oracle eBusiness Suite and shell scripting. Specialties: Oracle RAC & ASM (Configuration/Monitoring/Tuning) Oracle Data Guard (Configuration/Monitoring/Tuning) EM12c (Installation / troubleshooting) SQL & PLSQL *nix Shell scripting

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.