lunar@TSMISC02> create table t(id number, bitmap_flag varchar2(5));
Table created.
Elapsed: 00:00:00.01
lunar@TSMISC02> create bitmap index t_idx on t(bitmap_flag);
Index created.
Elapsed: 00:00:00.01
lunar@TSMISC02> select distinct sid from v$mystat;
SID
----------
16
Elapsed: 00:00:00.00
lunar@TSMISC02> insert into t values(1,'Y');
1 row created.
Elapsed: 00:00:00.00
另开一个窗口（session 18）：
lunar@TSMISC02> select distinct sid from v$mystat;
SID
----------
18
Elapsed: 00:00:00.00
lunar@TSMISC02> insert into t values(2,'Y');
。。。。。。
hang。。。
。。。。。。
回到session 16:
lunar@TSMISC02> select * from v$lock where block=1;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
56AF9100 56AF920C 16 TX 917516 2650 6 0 107 1
Elapsed: 00:00:00.10
lunar@TSMISC02> select * from v$lock where id1=917516;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
56AF9100 56AF920C 16 TX 917516 2650 6 0 131 1
56395E38 56395E48 18 TX 917516 2650 0 4 99 0
Elapsed: 00:00:00.01
可见，session 16 阻塞了 session 18.
在session16 提交刚才的insert操作，再观察：
lunar@TSMISC02> commit;
Commit complete.
Elapsed: 00:00:00.00
lunar@TSMISC02> select * from v$lock where block=1;
no rows selected
Elapsed: 00:00:00.01
lunar@TSMISC02> select * from t;
ID BITMA
---------- -----
1 Y
Elapsed: 00:00:00.00
再回到session 18 观察：
lunar@TSMISC02> insert into t values(2,'Y');
1 row created.
Elapsed: 00:02:43.49
lunar@TSMISC02> select * from t;
ID BITMA
---------- -----
1 Y
2 Y
Elapsed: 00:00:00.00
可见阻塞已经消除了。
