CREATE BLOCKING SESSION
To begin, create a situation where one user is actively blocking another. Open two sessions. Issue the following commands in Session 1 to build the test table:
** in sesion 1 create table and insert data also
SQL> create table shiera (feb varchar2(1), ais varchar2(1));
Table created.
SQL> insert into shiera values (1,'a');
1 row created.
SQL> insert into shiera values (2,'b');
1 row created.
SQL> commit;
Commit complete.
to grab a lock from whole table issue this command
SQL> select * from shiera for update;
F A
- -
1 a
2 b
** in session 2 issue this command,
SQL> update shiera set ais='c' where ais='b';
This statement will hang, blocked by the lock that Session 1 is holding on the entire table..
IDENTIFYING BLOCKING SESSION
The V$LOCK view is faster to query, makes it easy to identify the blocking session, and has a lot more information
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
C00000048619B320 C00000048619B348 7304 TM 118858 0 3 0 882 2
C00000048619B420 C00000048619B448 8135 TM 118858 0 3 0 758 2
C000000485BEBB70 C000000485BEBBA8 7304 TX 1310728 1184 6 0 882 1
Note the BLOCK column. If a session holds a lock that's blocking
another session, BLOCK=1. Further, you can tell which session is being
blocked by comparing the values in ID1 and ID2. The blocked session
will have the same values in ID1 and ID2 as the blocking session, and,
since it is requesting a lock it's unable to get, it will have REQUEST
> 0
to look with humanity you can use this qury to determine which session blocking another session
SQL> select s1.username || '@' || s1.machine
2 || ' ( SID=' || s1.sid || ' ) is blocking '
3 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;
BLOCKING_STATUS
--------------------------------------------------------------------------------
DBCHECK@bjmppdb2 ( SID=7304 ) is blocking DBCHECK@bjmppdb2 ( SID=8135 )
and with another query you can see the object being blocking whit this query, you can get object id from ID1 from v$lock
SQL> select owner, object_name from dba_objects where object_id='118858';
OWNER OBJECT_NAME
------------------------------ ----------------------------------
DBCHECK SHIERA
SQL> select owner, object_name from dba_objects where object_id='118858';
IDENTIFIED THE LOCKED ROW
With This qeury you can identifed the row id..
SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=8135
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------ -------------- -------------- --------------- ------------- ------------------
SHIERA 118858 4 462060 1 AAAdBKAAEAABwzsAAB
After that you can determine which row is being locked
SQL> select * from DBCHECK.SHIERA where rowid='AAAdBKAAEAABwzsAAB';
F A
- -
2 b
Okay.. may this make clear about blocking session
