session 1:
SQL> select * from tt;

ID NAME AGE
---------- ---------- ----------
1 c 30
2 b

SQL> select distinct sid from v$mystat;

SID
----------
159

SQL> update tt set name='a' where id=1;

已更新 1 行。

SQL>
--============================
session 2:
SQL> select distinct sid from v$mystat;

SID
----------
142

SQL> update tt set age=30 where id=1;
等待
--==================================
session 3:(监控session)
SQL> set linesize 200
--先确定阻塞的session和被阻塞的session，由字段BLOCK可知session 159阻塞了session 142
SQL> select * from v$lock where block=1 or request<>0
2 ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6C4343B4 6C4343C8 142 TX 1310753 18 0 6 132 0
6BA3C518 6BA3C634 159 TX 1310753 18 6 0 147 1

SQL>
--再根据上面知道的sid确定update操作所需要在表上以及行上加的lock type(TM,TX),当然最终阻塞是发生在row上：
SQL> select * from v$lock where sid in (142,159) order by sid;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
6B9C4D08 6B9C4D20 142 TM 10247 0 3 0 222 0
6C4343B4 6C4343C8 142 TX 1310753 18 0 6 222 0
6BA3C518 6BA3C634 159 TX 1310753 18 6 0 237 1
6B9C4C44 6B9C4C5C 159 TM 10247 0 3 0 237 0

SQL>
SQL> select * from dba_locks where session_id in (142,159) order by session_id;

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS
---------- --------------- ---------- ---------- ---------- ---------- ------------ ----------------------------------------
142 DML Row-X (SX) None 10247 0 4176 Not Blocking
142 Transaction None Exclusive 1310753 18 4176 Not Blocking
159 Transaction Exclusive None 1310753 18 4191 Blocking
159 DML Row-X (SX) None 10247 0 4191 Not Blocking

SQL>
--TM,TX的含义,TM锁是由DML操作引起的加在表上的锁，TX是由trasaction引起的夹在row上的锁：
SQL> select * from v$lock_type where type in ('TM','TX');

TYP NAME ID1_TAG ID2_TAG IS_ DESCRIPTION
--- --------------- --------------- --------------- --- ----------------------------------------------------------------------
TM DML object # table/partition YES Synchronizes accesses to an object
TX Transaction usn<<16 | slot sequence YES Lock held by a transaction to allow other transactions to wait for it

SQL>
--TM锁对应的ID1表示被锁定的对象object_id：
SQL> select object_id,object_name from dba_objects where object_id=10247;

OBJECT_ID OBJECT_NAM
---------- ----------
10247 TT

SQL>
--TX锁对应的ID2=1310753的含义，ID2是由4个字节组成的，高位2个字节和低位2个字节分别表示v$trasaction中的xidusn,xidslot：
SQL> select xidusn,xidslot from v$transaction;

XIDUSN XIDSLOT
---------- ----------
20 33

SQL> select to_char(1310753,'xxxxxxx') from dual;

TO_CHAR(
--------
140021

SQL>
--其中高位字节(00)14转为10进制数是20，低位字节0021转为10进制数是33；另外我们
也发现这2个session引起的锁TX锁的ID1和ID2是相同的,也就是说最终这2个session里
执行的update操作产生的事务id是相同的，在db里同一时刻我想xid必须是唯一的吧，所以
这个xid到底是阻塞的原因还是结果呢？
SQL> select id1,id2 from v$lock where sid in (142,159) and type='TX';

ID1 ID2
---------- ----------
1310753 18
1310753 18

SQL> select xid from v$transaction;

XID
----------------
1400210012000000

SQL>
--这里的14对应的就是v$transaction中XIDUSN,0021对应的就是XIDSLOT,0012对应的就是XIDSQN
--================================
--从session中获得更为详细的和阻塞相关的session以及rowid信息：
SQL> select sid,taddr,lockwait,status,sql_id,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,blocking_session_status,blocking_instance b_in
,blocking_session b_se from v$session where sid in (142,159);

SID TADDR LOCKWAIT STATUS SQL_ID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# BLOCKING_SE B_IN B_SE
---------- -------- -------- -------- ------------- ------------- -------------- --------------- ------------- ----------- ---------- ----------
142 6BA36C5C 6C4343C8 ACTIVE 9zr2gjp8darff 10247 5 2064 0 VALID 1 159
159 6BA3C518 INACTIVE 10247 5 2060 0 NO HOLDER

SQL>
--从上面查询更加清楚的知道阻塞session 159位于instance 1上，正在被阻塞的session是142，
同时我们也知道被阻塞session当前执行的sql_id是9zr2gjp8darff，从而知道对应的sql是：
SQL> select sql_text from v$sql where sql_id='9zr2gjp8darff';

SQL_TEXT
----------------------------------------
update tt set age=30 where id=1

SQL>
--当然根据字段row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#我们也知道阻塞到底是发生在哪一行上(通过计算rowid)：
SQL> select dbms_rowid.rowid_create(1,10247,5,2064,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAACgHAAFAAAAgQAAA

SQL> select id,name,rowid from tt where rowid='AAACgHAAFAAAAgQAAA';

ID NAME ROWID
---------- --------------- ------------------
1 c AAACgHAAFAAAAgQAAA

SQL>
--================================
--事务信息：
SQL> select addr,xidusn,xidslot,xidsqn,status,ses_addr,xid from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN STATUS SES_ADDR XID
-------- ---------- ---------- ---------- ---------------- -------- ----------------
6BA3C518 20 33 18 ACTIVE 6CF3F284 1400210012000000

SQL> select * from v$transaction_enqueue;

ADDR KADDR SID TYP ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- --- ---------- ---------- ---------- ---------- ---------- ----------
6BA3C518 6BA3C634 159 TX 1310753 18 6 0 2859 1

SQL>