Wednesday, June 18, 2008

Find out Blocking Session

I came across this pieace of code in Oracle form which helps us to find the blocking sessions. I tought of sharing it here.


SQL> SET ECHO OFF
SQL> SET LINE 10000
SQL> COLUMN BLOCKER FORMAT A11;
SQL> COLUMN BLOCKEE FORMAT A10;
SQL> COLUMN SID FORMAT 99999;
SQL> COLUMN BLOCKER_MODULE FORMAT A15 TRUNC;
SQL> COLUMN BLOCKEE_MODULE FORMAT A15 TRUNC;
SQL>
SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

Session altered.

SQL>
SQL> SELECT a.inst_id,
2 (SELECT username FROM gv$session s WHERE s.inst_id=a.inst_id AND s.sid=a.sid) blocker,
3 a.sid,
4 (SELECT module FROM gv$session s WHERE s.inst_id=a.inst_id AND s.sid=a.sid) blocker_module ,
5 ' is blocking ' "IS BLOCKING",
6 b.inst_id,
7 (SELECT username FROM gv$session s WHERE s.inst_id=b.inst_id AND s.sid=b.sid) blockee,
8 b.sid ,
9 (SELECT module FROM gv$session s WHERE s.inst_id=b.inst_id AND s.sid=b.sid) blockee_module
10 FROM gv$lock a, gv$lock b
11 where a.block <>0
12 AND b.request > 0
13 AND a.id1 = b.id1
14 AND a.id2 = b.id2
15 AND a.sid <> b.sid
16 order by 1, 2
17 /

INST_ID BLOCKER SID BLOCKER_MODULE IS BLOCKING INST_ID BLOCKEE SID BLOCKEE_MODULE
---------- ----------- ------ --------------- ------------- ---------- ---------- ------ ---------------
1 SYSADM 15 SQL*Plus is blocking 1 SYSADM 11 T.O.A.D.

SQL>
SQL> ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

Session altered.

SQL> BEGIN
2 dbms_output.enable(1000000);
3 FOR do_loop IN (SELECT session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
4 b.object_name object_name, b.object_type object_type
5 FROM v$locked_object a, dba_objects b
6 WHERE xidsqn != 0
7 and b.object_id = a.object_id)
8 LOOP
9 dbms_output.put_line('.');
10 dbms_output.put_line('Blocking Session : '||do_loop.session_id);
11 dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
12 dbms_output.put_line('Object Type : '||do_loop.object_type);
13 FOR next_loop IN (SELECT sid
14 FROM v$lock
15 WHERE id2 = do_loop.xidsqn
16 AND sid != do_loop.session_id)
17 LOOP
18 dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
19 END LOOP;
20 END LOOP;
21 END;
22 /
.
Blocking Session : 15
Object (Owner/Name): SYSADM.T
Object Type : TABLE
Sessions being blocked : 11

PL/SQL procedure successfully completed.

SQL>

No comments: