SESSION 1:
alter table wxh_tbd drop column object_name;
SESSION 2:
alter session set events 'immediate trace name library_cache level 10';
查看dump文件,搜索APOLLO.WXH_TBD,看看执行这个DDL过程中,会在表上加什么类型的DDL锁。
LIBRARY HANDLE:0x98a1cfc0 bid=21635 hid=96705483 lmd=S pmd=S sta=VALD
name=APOLLO.WXH_TBD
hash=a8a00ab51c4808690d3aacc096705483 idn=171692
tim=08-10-2011 13:18:02 kkkk-dddd-llll=0000-0701-0701
exc=0 ivc=0 ldc=4 slc=1 lct=23 pct=25
cbb=3 rpr=3 kdp=0 kep=0 bus=21 hus=21 dbg=0
dmtx=0x98a1d068(0, 20, 0) mtx=0x98a1d0d0(2505, 226, 0)
nsp=TABL(01) typ=TABL(02) llm=0 flg=KGHP/TIM/[00002800]
lwt=0x98a1d048[0x98a1d048,0x98a1d048]
pwt=0x98a1d028[0x98a1d028,0x98a1d028]
ref=0x98a1d058[0x8ab4b828,0x7c757258]
HANDLE REFERENCES:
reference handle flags
--------- --------- -------------------
8ab4b828 871d21c0 DEP[01]
timestamp=08-10-2011 13:18:02 whr=0
7c757258 b36001d0 DEP/INV[05]
timestamp=08-10-2011 13:17:23 whr=8
LOCK OWNERS:
lock user session count mode flags
-------- -------- -------- ----- ---- ------------------------
870d5030 ae6ba8e8 ae6ba8e8 1 S CNB/[01]-----------------------------加的S模式的library cache lock
PIN OWNERS:
pin user session lock count mode mask
-------- -------- -------- -------- ----- ---- ----
870d4f38 ae6ba8e8 ae6ba8e8 0 1 S 0701-----------------------加的S模式的library cache pin
LIBRARY OBJECT: 0x9226d0d8
flg=EXS/LOC[0005] pfl=[0000] ssta=VALD
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 87a58228 9226d1b8 9226d0a8 I/P/A/-/- 0 NONE 00
8 9226d408 772dde50 772ddc38 I/P/A/-/- 1 NONE 00
9 84fc9d28 772dd1b8 772dcf30 I/P/A/-/- 1 NONE 00
10 84fc9df0 77290d58 772909f0 I/P/A/-/- 1 NONE 00
由于在表共享池对象上加的s模式的锁,因此对这个表的查询和修改不会赌在共享池对象上。也就是不会导致library cache lock/pin
但是无论如何,插入操作会被阻塞,因为这个操作在表上加了模式为6的TM锁。而DML操作需要获得表上模式为3的TM锁,这两个锁是互斥的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-704582/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22034023/viewspace-704582/