现象模拟：

session1:

create index GLOBAL_ACCOUNT_BIZ_gid_ind on GLOBAL_ACCOUNT_BIZ ( GLOBAL_ID);

alter table GLOBAL_ACCOUNT_BIZ add constraint GLOBAL_ACCOUNT_BIZ_GID_IND primary key (GLOBAL_ID) using index;

session2:

select * from GLOBAL_ACCOUNT_BIZ;

session 2会被hang住。

session 3:

dump出此时的共享池内容。表的handler上显示存在X模式的LOCK和X模式的PIN.

BUCKET#2429 mtx=0xa229cdf8(0, 9894, 0):
  LIBRARY HANDLE:0x9acc9368 bid=2429 hid=9184097d lmd=X pmd=X sta=VALD
  name=APOLLO.GLOBAL_ACCOUNT_BIZ
  hash=8d850df7ee7a0f75e945d62c9184097d idn=12110
  tim=01-19-2010 15:56:05  kkkk-dddd-llll=0000-0741-0741
  exc=0 ivc=0 ldc=30 slc=1 lct=293 pct=295
  cbb=36 rpr=1 kdp=0 kep=0 bus=9468 hus=9468 dbg=0
  dmtx=0x9acc9410(0, 332, 0) mtx=0x9acc9478(3262, 12474, 0)
  nsp=TABL(01) typ=TABL(02) llm=0 flg=KGHP/TIM/[00002800]
  lwt=0x9acc93f0[0x9acc93f0,0x9acc93f0]
  pwt=0x9acc93d0[0x9acc93d0,0x9acc93d0]
  ref=0x9acc9400[0x9887ad10,0x98aad400]
    HANDLE REFERENCES:
    reference   handle   flags
    --------- ---------  -------------------
     9887ad10  9aaa9a90  DEP[01]
     989c28f0  9ab05cf0  DEP[01]
 timestamp=01-19-2010 15:56:05 whr=0
     98aad400  9acd5020  DEP[01]
 timestamp=01-19-2010 15:56:05 whr=0
    LOCK OWNERS:
        lock     user  session count mode       flags
    -------- -------- -------- ----- ---- ------------------------
    8e8c0c68 ae987670 ae987670     1 X    CNB/[01]
    PIN OWNERS:
         pin     user  session     lock count mode mask
    -------- -------- -------- -------- ----- ---- ----
    8e8c0b70 ae987670 ae987670        0     1 X    0701
    LIBRARY OBJECT: 0x98877838
    flg=EXS/LOC/UPD[0905] pfl=[0000] ssta=VALD
    DATA BLOCKS:
    data#     heap  pointer    status pins change whr
    ----- -------- -------- --------- ---- ------ ---
        0 9ab2a320 98877918 98877808 I/P/A/-/-    0 NONE   00
        8 98877b28 9d45ef50 9d45ed38 I/P/A/-/-    1 NONE   00
        9 89952dc8 8a2f8538 8a2f82b0 I/P/A/-/-    1 NONE   00
       10 89952e50 8a6246a8 8a624340 I/P/A/-/-    1 NONE   00

SQL> select event,p1,p1raw,p2,p3 from v$session_wait where wait_class<>'Idle';

EVENT                                  P1 P1RAW                    P2         P3
------------------------------ ---------- ---------------- ---------- ----------
SQL*Net message to client      1650815232 0000000062657100          1          0
library cache lock             2597098344 000000009ACC9368 2596927736        201
db file scattered read                 10 000000000000000A     136490         15

p1raw代表的是handler的地址，可以查出是哪个对象上面的library cache lock.

SQL> select KGLNAOBJ from x$kglob where kglhdadr = '000000009ACC9368';

KGLNAOBJ
--------------------------------------------------------------------------------
GLOBAL_ACCOUNT_BIZ

可见，对表增加主键的操作，会需要获取表handler上X模式的LOCK，即library cache lock.而select需要获取表上s模式的lock。因此发生冲突。

解决的办法是先创建一个唯一索引，再进行增加主键的操作。这样会缩短锁表时间。当然此字段需要有非空约束。