ELECT DISTINCT
s1.username ||'@'|| s1.machine ||'(INST=' || s1.inst_id ||' SID_SERIAL#= '|| s1.sid ||'_'||s1.serial#||')' AS Blocker
,s2.username||'@'||s2.machine||' (INST='||s1.inst_id||' SID_SERIAL# = '||s2.sid ||'_'||s2.serial#||' Event='|| s2.event ||')' AS Blockee
, s2.program ||'@'|| s1.machine AS Program
,SUBSTR(ob.object_name,1,40) ||' SQL -> ' || s3.sql_text AS Blocked_OBJ_SQL, s4.sql_text AS Blocker_SQL
FROM gv$lock l1, gv$session s1, gv$lock l2
, gv$session s2, v$sql s3
, gv$active_session_history ash, v$sql s4, dba_objects ob, gv$locked_object lo
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
AND s2.sql_id = s3.sql_id
AND s1.prev_sql_id = s4.sql_id
AND lo.object_id = ob.object_id
AND lo.SESSION_ID = s2.sid
AND s2.seconds_in_wait > 10;
Note
1. this is using global view of GV$SESSION_WAIT – and reports back on any session which has waited > 10 seconds. So if your case scenario is looking for sessions which are blocked for less period of time then change the final line in the above script as required.
AND s2.seconds_in_wait > 10;
Be cautious when amending this time period, as some applications may have by design, plenty of transactions which do cause row lock transactions eg scheduling applications. Normally, the purpose of this script is to look for anomalies.
2. seconds_in_wait has been deprecated from oracle 11g+. Currently is still works in 11gR2. Future releases may not include this column
if this bothers you, then change script line :
AND s2.seconds_in_wait > 10;
to read
AND s2.WAIT_TIME_MICRO/1000000 > 10;
Test case
We can easily simulate a a blocking lock situation using a test table. We will open two sqlplus sessions to the database and then issue the following commands to demonstrate this
1. using sqlplus connect to the database, eg
C:\sqlplus [username]/[password]@database_name
SQL*Plus: RELEASE 11.2.0.1.0 Production ON Wed Oct 5 13:33:26 2011
Copyright (c) 1982, 2010, Oracle. ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition RELEASE 11.2.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, Data Mining AND REAL Application Testing options
SQL>
Session A.
here, we create a dummy table and insert some test data into it. Check the data via a select, and then lock all rows in the table :
- create table test(colA varchar2(1), colB varchar2(1));
- insert into test values (1,’a’);
- insert into test values (2,’b’);
- select * from test ;
- commit;
- select * from test for update;
SQL> CREATE TABLE test(colA VARCHAR2(1), colB VARCHAR2(1));
TABLE created.
SQL> INSERT INTO test VALUES (1,'a');
1 ROW created.
SQL> INSERT INTO test VALUES (1,'b');
1 ROW created.
SQL> SELECT * FROM test ;
C C
- -
1 a
1 b
SQL> COMMIT;
COMMIT complete.
sql> SELECT * FROM test FOR UPDATE;
now we have locked all rows in the table.
so switching to another sqlplus session, using the same connection string as before
eg
C:\sqlplus [username]/[password]@database_name
SQL>
lets first try and see if we can select any row data :
SQL> SELECT * FROM test ;
C C
- -
1 a
1 b
good – expected behaviour as a row or table lock does not normally prevent us from seeing the before image. Now lets try updating a row :
SQL> UPDATE test SET colA='Z' WHERE colA='1';
now you should see the session hangs. And you can prove this from running the script as above giving us the example result of :
so back to Session A – lets close the lock
SQL> COMMIT;
COMMIT complete.
and then checking session B, you should see that the update has now completed :
SQL> UPDATE test SET colA='Z' WHERE colA='1';
1 ROW updated.
Big Nose Kates 