[Oracle]如何观察Table 的各种Lock 之间的冲突

举例：

Session#15 创建表：

SID 15

==============

create table t1 (c1 number)

partition by range(c1)

(partition p1 values less than('11'),

partition p2 values less than('21'),

partition p3 values less than('31')

)

/

Session#15 对表给予最大限度的锁定(exclusive)

lock table t1 in exclusive mode; (T1 X(6) lock)

Session#137 进行其他操作(alter table add partition)

SID 137

==============

alter table t1 add partition p4 values less than ('41'); ( T1 S(4) lock, T1 X(60 无法共容，所以被阻塞,会一直停在那里)

查看锁的状况

SYS> select * from v$locked_object;

   XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS                  LOCKED_MODE

---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------

        0          0          0      87656         15 JIMMY                          ora11204                       698                                6

        3         13       1056      87656        137 JIMMY                          ora11204                       854                                0

        3         13       1056         18        137 JIMMY                          ora11204                       854                                3

这时我们可以执行其他操作，反正大家都被最大级别的 Exclusive 锁所阻塞，这时候就可以看看他们所执行的 lock 的 locked_mode，

看各种操作的锁类型是否相容。

SID#141

==============

insert into t1 values(28);

SYS> select * from v$locked_object;

   XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS                  LOCKED_MODE

---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------

        6         23       1234      87659        141 JIMMY                          ora11204                       927                                3

        6         23       1234      87656        141 JIMMY                          ora11204                       927                                3

   ...