[Update 2011-07] Oracle 11.2.0.2 enqueues dump trace is quite different. For example, after one session: lock table t in share mode; and another session: lock table t in row share mode; and another session: lock table t in share mode; alter session set events 'immediate trace name enqueues level 10'; The trace file has the following (lines beginning with ! are mine): ******************************************************************************* TM-000077f6-00000000 res: 0x9c6bed40 , flags: (0x6) ALO/-/USR/-/-/- !lock is allocated, not global (not RAC), user-requested lock, ... held counts: NULL=0 , SS=1 , SX=0 , S=2 , SSX=0 , X=0 (bmask: 0x14) !table locked in SS mode by 1 session, locked in S mode by 2 !bitmask is 0x14 or 0 0 1 0 1 0 0 in binary ! X SSX S SX SS NULL ^-may be no lock at all? !Bit is 0 or 1; when 1, there may be more than 1 session in this mode. lock value: 00000000 00000000 00000000 00000000 htb(0x9c6bed40): [9c6ef950,9c6ef950] own(0x9c6bed50): [9af0d930,9af0da90] cnv(0x9c6bed70): [9c6bed70,9c6bed70] wtr(0x9c6bed60): [9c6bed60,9c6bed60] !htb? owner, converter, waiter queues lock summary: OWN: L1:S , L2:SS , L3:S CNV: WTR: # of incomp w/ SS converters: 0 # of incomp w/ SS waiters: 0 lock details: lock lst hold wait sid:ser owner link ------------------------------------------------------------------------- L1: 0x9af0d920 OWN S -- 33:16623 0x9ceb9ec0 [9af95948,9c6bed50] L2: 0x9af95938 OWN SS -- 50:9411 0x9ce87520 [9af0da90,9af0d930] L3: 0x9af0da80 OWN S -- 47:45233 0x9ce90400 [9c6bed50,9af95948] ******************************************************************************* [End of Update 2011-07] !oradebug dump enqueues 10 !or !alter session set events 'immediate trace name enqueues level 10'; !generated this trace file !May need to combine with 10704 event trace to get more insight Dump file c:\oracle\admin\orcl9i\udump\orcl9i_ora_3112.trc Sun Sep 10 09:02:11 2006 ORACLE V9.2.0.5.0 - Production vsnsta=0 vsnsql=12 vsnxtr=3 Windows 2000 Version 5.1 Service Pack 2, CPU type 586 Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production Windows 2000 Version 5.1 Service Pack 2, CPU type 586 Instance name: orcl9i Redo thread mounted by this instance: 1 Oracle process number: 11 Windows thread id: 3112, image: ORACLE.EXE *** 2006-09-10 09:02:11.233 *** SESSION ID:(8.10) 2006-09-10 09:02:11.013 ...[previous dump in the same session snipped].. *** 2006-09-10 09:50:20.708 =================================================== ENQUEUES -------- Active: lock: 00000000, resource: 00000000 Global: name: ORCL9I Hash: length: 375, at: 67722260 ! 375 is _enqueue_hash, i.e. enqueue hash table length. 375 array entries below [67722260,67722260] [67722278,67722278] [67722290,67722290] [677222a8,677222a8] [677222c0,677222c0] [677222d8,677222d8] [677222f0,677222f0] [67722308,67722308] [67722320,67722320] [67722338,67722338] [67722350,67722350] [67722368,67722368] [67722380,67722380] [67722398,67722398] [677223b0,677223b0] [677223c8,677223c8] [677223e0,677223e0] [677223f8,677223f8] [67722410,67722410] [67722428,67722428] [67722440,67722440] [67722458,67722458] [67722470,67722470] [67722488,67722488] [677224a0,677224a0] [677224b8,677224b8] [677224d0,677224d0] [677224e8,677224e8] [67722500,67722500] [67722518,67722518] [67722530,67722530] [67722548,67722548] [67722560,67722560] [67722578,67722578] [67722590,67722590] [677225a8,677225a8] [677225c0,677225c0] [677225d8,677225d8] [677225f0,677225f0] [67722608,67722608] [67722620,67722620] [67722638,67722638] [67722650,67722650] [67722668,67722668] [67722680,67722680] [67722698,67722698] [677226b0,677226b0] [677226c8,677226c8] [677226e0,677226e0] [677226f8,677226f8] [67722710,67722710] [67722728,67722728] [67722740,67722740] [67722758,67722758] [67722770,67722770] [67722788,67722788] [677227a0,677227a0] [677227b8,677227b8] [677227d0,677227d0] [677227e8,677227e8] [67722800,67722800] [67722818,67722818] [67722830,67722830] [67722848,67722848] [67722860,67722860] [67722878,67722878] [67722890,67722890] [677228a8,677228a8] [677228c0,677228c0] [677228d8,677228d8] [677228f0,677228f0] [67722908,67722908] [67722920,67722920] [67722938,67722938] [67722950,67722950] [67722968,67722968] [67722980,67722980] [67722998,67722998] [677229b0,677229b0] [677229c8,677229c8] [677229e0,677229e0] [677229f8,677229f8] [67722a10,67722a10] [67722a28,67722a28] [6770dc90,6770dc90] [67722a58,67722a58] [67722a70,67722a70] [67722a88,67722a88] [67722aa0,67722aa0] [67722ab8,67722ab8] [67722ad0,67722ad0] [67722ae8,67722ae8] [67722b00,67722b00] [67722b18,67722b18] [67722b30,67722b30] [67722b48,67722b48] [67722b60,67722b60] [67722b78,67722b78] [67722b90,67722b90] [67722ba8,67722ba8] [67722bc0,67722bc0] [67722bd8,67722bd8] [67722bf0,67722bf0] [67722c08,67722c08] [67722c20,67722c20] [67722c38,67722c38] [67722c50,67722c50] [67722c68,67722c68] [67722c80,67722c80] [67722c98,67722c98] [67722cb0,67722cb0] [67722cc8,67722cc8] [67722ce0,67722ce0] [67722cf8,67722cf8] [67722d10,67722d10] [67722d28,67722d28] [67722d40,67722d40] [67722d58,67722d58] [67722d70,67722d70] [67722d88,67722d88] [67722da0,67722da0] [67722db8,67722db8] [6770e1d0,6770e1d0] [67722de8,67722de8] [67722e00,67722e00] [67722e18,67722e18] [67722e30,67722e30] [67722e48,67722e48] [67722e60,67722e60] [67722e78,67722e78] [67722e90,67722e90] [67722ea8,67722ea8] [67722ec0,67722ec0] [67722ed8,67722ed8] [67722ef0,67722ef0] [6770f060,6770f060] [67722f20,67722f20] [67722f38,67722f38] [67722f50,67722f50] [67722f68,67722f68] [67722f80,67722f80] [67722f98,67722f98] [67722fb0,67722fb0] [67722fc8,67722fc8] [67722fe0,67722fe0] [677102d0,677102d0] [67723010,67723010] [67723028,67723028] [67723040,67723040] [6770fce8,6770fce8] [67723070,67723070] [67723088,67723088] [677230a0,677230a0] [677230b8,677230b8] [677230d0,677230d0] [677230e8,677230e8] [67723100,67723100] [67723118,67723118] [67723130,67723130] [67723148,67723148] [67723160,67723160] [67723178,67723178] [67723190,67723190] [677231a8,677231a8] [677231c0,677231c0] [677231d8,677231d8] [677231f0,677231f0] [67723208,67723208] [67723220,67723220] [67723238,67723238] [67723250,67723250] [67723268,67723268] [67723280,67723280] [67723298,67723298] [677232b0,677232b0] [677232c8,677232c8] [677232e0,677232e0] [677232f8,677232f8] [67723310,67723310] [67723328,67723328] [67723340,67723340] [67723358,67723358] [67723370,67723370] [67723388,67723388] [677233a0,677233a0] [677233b8,677233b8] [677233d0,677233d0] [677233e8,677233e8] [67723400,67723400] [67723418,67723418] [67723430,67723430] [67723448,67723448] [67723460,67723460] [67723478,67723478] [67723490,67723490] [677234a8,677234a8] [677234c0,677234c0] [677234d8,677234d8] [677234f0,677234f0] [67723508,67723508] [67723520,67723520] [67723538,67723538] [67723550,67723550] [67723568,67723568] [67723580,67723580] [67723598,67723598] [677235b0,677235b0] [677235c8,677235c8] [677235e0,677235e0] [677235f8,677235f8] [67723610,67723610] [67723628,67723628] [67723640,67723640] [67723658,67723658] [67723670,67723670] [67723688,67723688] [677236a0,677236a0] [677236b8,677236b8] [677236d0,677236d0] [677236e8,677236e8] [67723700,67723700] [67723718,67723718] [67723730,67723730] [67723748,67723748] [67723760,67723760] [67723778,67723778] [67723790,67723790] [677237a8,677237a8] [677237c0,677237c0] [677237d8,677237d8] [677237f0,677237f0] [67723808,67723808] [67723820,67723820] [67723838,67723838] [67723850,67723850] [67723868,67723868] [67723880,67723880] [67723898,67723898] [677238b0,677238b0] [677238c8,677238c8] [677238e0,677238e0] [677238f8,677238f8] [67723910,67723910] [67723928,67723928] [67723940,67723940] [67723958,67723958] [67723970,67723970] [67723988,67723988] [677239a0,677239a0] [677239b8,677239b8] [677239d0,677239d0] [677239e8,677239e8] [67723a00,67723a00] [67723a18,67723a18] [67723a30,67723a30] [67723a48,67723a48] [67723a60,67723a60] [67723a78,67723a78] [67723a90,67723a90] [67723aa8,67723aa8] [67723ac0,67723ac0] [67723ad8,67723ad8] [67723af0,67723af0] [67723b08,67723b08] [67723b20,67723b20] [67723b38,67723b38] [67723b50,67723b50] [67723b68,67723b68] [67723b80,67723b80] [67723b98,67723b98] [67723bb0,67723bb0] [67723bc8,67723bc8] [67723be0,67723be0] [67723bf8,67723bf8] [67723c10,67723c10] [67723c28,67723c28] [67723c40,67723c40] [6770db40,6770db40] [67723c70,67723c70] [67723c88,67723c88] [67723ca0,67723ca0] [67723cb8,67723cb8] [67723cd0,67723cd0] [67710f68,67710f68] [67723d00,67723d00] [67723d18,67723d18] [67723d30,67723d30] [67723d48,67723d48] [67723d60,67723d60] [67723d78,67723d78] [67723d90,67723d90] [67723da8,67723da8] [67723dc0,67723dc0] [67723dd8,67723dd8] [67723df0,67723df0] [67723e08,67723e08] [67723e20,67723e20] [6770e128,6770e128] [6770e17c,6770e17c] [6770dd8c,6770dd8c] [6770dde0,6770dde0] [6770de34,6770de34] [6770de88,6770de88] [6770dedc,6770dedc] [6770df30,6770df30] [6770df84,6770df84] [6770dfd8,6770dfd8] [67723f28,67723f28] [67723f40,67723f40] [67723f58,67723f58] [67723f70,67723f70] [67723f88,67723f88] [67723fa0,67723fa0] [67723fb8,67723fb8] [67723fd0,67723fd0] [67723fe8,67723fe8] [67724000,67724000] [67724018,67724018] [67724030,67724030] [67724048,67724048] [67724060,67724060] [67724078,67724078] [67724090,67724090] [677240a8,677240a8] [677240c0,677240c0] [677240d8,677240d8] [677240f0,677240f0] [67724108,67724108] [67724120,67724120] [67724138,67724138] [67724150,67724150] [67724168,67724168] [67724180,67724180] [67724198,67724198] [677241b0,677241b0] [677241c8,677241c8] [677241e0,677241e0] [677241f8,677241f8] [67724210,67724210] [67724228,67724228] [67724240,67724240] [67724258,67724258] [67724270,67724270] [67724288,67724288] [677242a0,677242a0] [677242b8,677242b8] [677242d0,677242d0] [677242e8,677242e8] [67724300,67724300] [67724318,67724318] [67724330,67724330] [67724348,67724348] [67724360,67724360] [67724378,67724378] [67724390,67724390] [677243a8,677243a8] [677243c0,677243c0] [677243d8,677243d8] [677243f0,677243f0] [67724408,67724408] [67724420,67724420] [67724438,67724438] [67724450,67724450] [67724468,67724468] [67724480,67724480] [67724498,67724498] [677244b0,677244b0] [677244c8,677244c8] [677244e0,677244e0] [677244f8,677244f8] [67724510,67724510] [67724528,67724528] [67724540,67724540] [67724558,67724558] [67724570,67724570] res identification SS SX S SSX X md link owners converters waiters ------------------------------------------------------------------------- 6770DB40 XR-00000004-00000000 1 0 0 0 0 0 2 [67723c58,67723c58] [676e3bbc,676e3bbc] [6770db58,6770db58] [6770db50,6770db50] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3BB4 OWN 676308DC (004) NULL NLCK 1 [6770db48,6770db48] !Each entry from this point on starts with resource ID (like 6770DB40 above), !which is v$resource.addr, then type-ID1-ID2, then 6 bits identifying mode of !lock held on this resource (null, SS, SX, S, SSX and X). The above XR resource !is held in null mode by 1 owner (1 followed by 5 0's); the number is number of !resource owners in this mode. md=2 is the bit mask; see 11.2.0.2 trace at the !beginning. !Next line is owners, converters and waiters queues, respectively. !The last line is lock address (v$lock.kaddr), which queue this is on, session !address (v$session.saddr) and sid (in parentheses), lock mode held and waited !for (if null mode, called "NULL"; "NLCK" probably means no lock at all). ser !should be v$session.serial#. The last part is waiters list on this resource. 6770DC90 RT-00000001-00000000 0 0 0 0 0 1 64 [67722a40,67722a40] [676e3cec,676e3cec] [6770dca8,6770dca8] [6770dca0,6770dca0] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3CE4 OWN 6762FF6C (003) X NLCK 1 [6770dc98,6770dc98] 6770DD8C MR-00000003-00000000 0 0 0 1 0 0 16 [67723e68,67723e68] [676e3e68,676e3e68] [6770dda4,6770dda4] [6770dd9c,6770dd9c] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3E60 OWN 6762F5FC (002) S NLCK 1 [6770dd94,6770dd94] 6770DDE0 MR-00000004-00000000 0 0 0 1 0 0 16 [67723e80,67723e80] [676e3eb4,676e3eb4] [6770ddf8,6770ddf8] [6770ddf0,6770ddf0] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3EAC OWN 6762F5FC (002) S NLCK 1 [6770dde8,6770dde8] 6770DE34 MR-00000005-00000000 0 0 0 1 0 0 16 [67723e98,67723e98] [676e3f00,676e3f00] [6770de4c,6770de4c] [6770de44,6770de44] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3EF8 OWN 6762F5FC (002) S NLCK 1 [6770de3c,6770de3c] 6770DE88 MR-00000006-00000000 0 0 0 1 0 0 16 [67723eb0,67723eb0] [676e3f4c,676e3f4c] [6770dea0,6770dea0] [6770de98,6770de98] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3F44 OWN 6762F5FC (002) S NLCK 1 [6770de90,6770de90] 6770DEDC MR-00000007-00000000 0 0 0 1 0 0 16 [67723ec8,67723ec8] [676e3f98,676e3f98] [6770def4,6770def4] [6770deec,6770deec] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3F90 OWN 6762F5FC (002) S NLCK 1 [6770dee4,6770dee4] 6770DF30 MR-00000008-00000000 0 0 0 1 0 0 16 [67723ee0,67723ee0] [676e3fe4,676e3fe4] [6770df48,6770df48] [6770df40,6770df40] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3FDC OWN 6762F5FC (002) S NLCK 1 [6770df38,6770df38] 6770DF84 MR-00000009-00000000 0 0 0 1 0 0 16 [67723ef8,67723ef8] [676e4030,676e4030] [6770df9c,6770df9c] [6770df94,6770df94] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E4028 OWN 6762F5FC (002) S NLCK 1 [6770df8c,6770df8c] 6770DFD8 MR-0000000a-00000000 0 0 0 1 0 0 16 [67723f10,67723f10] [676e407c,676e407c] [6770dff0,6770dff0] [6770dfe8,6770dfe8] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E4074 OWN 6762F5FC (002) S NLCK 1 [6770dfe0,6770dfe0] 6770E128 MR-00000001-00000000 0 0 0 1 0 0 16 [67723e38,67723e38] [676e3dd0,676e3dd0] [6770e140,6770e140] [6770e138,6770e138] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3DC8 OWN 6762F5FC (002) S NLCK 1 [6770e130,6770e130] 6770E17C MR-00000002-00000000 0 0 0 1 0 0 16 [67723e50,67723e50] [676e3e1c,676e3e1c] [6770e194,6770e194] [6770e18c,6770e18c] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3E14 OWN 6762F5FC (002) S NLCK 1 [6770e184,6770e184] 6770E1D0 MR-000000c9-00000000 0 0 0 1 0 0 16 [67722dd0,67722dd0] [676e40c8,676e40c8] [6770e1e8,6770e1e8] [6770e1e0,6770e1e0] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E40C0 OWN 6762F5FC (002) S NLCK 1 [6770e1d8,6770e1d8] 6770F060 TA-00000006-00000001 0 0 0 0 0 1 64 [67722f08,67722f08] [676e3c08,676e3c08] [6770f078,6770f078] [6770f070,6770f070] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3C00 OWN (null) X NLCK [6770f068,6770f068] 6770FCE8 TS-00000002-00000001 0 0 1 0 0 0 8 [67723058,67723058] [676e3d38,676e3d38] [6770fd00,6770fd00] [6770fcf8,6770fcf8] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3D30 OWN 6763124C (005) SX NLCK 1 [6770fcf0,6770fcf0] 677102D0 DL-00006cf5-00000000 0 0 1 0 0 0 8 [67722ff8,67722ff8] [676e3c54,676e3c54] [677102e8,677102e8] [677102e0,677102e0] lock que session resource hold wait ser link ---------------------------------------------------------------------- 676E3C4C OWN 6763417C (010) SX NLCK 8 [677102d8,677102d8] !Session 10 (serial# 10) holds an SX lock of type DL (direct loader index creation) !because it tries to rebuild an index online and is held waiting. 67710F68 TM-00006cf5-00000000 U 0 0 0 0 0 1 64 [67723ce8,67723ce8] [67f44074,67f44074] [67710f80,67710f80] [67f440f8,67f44200] lock que session resource hold wait ser link ---------------------------------------------------------------------- 67F4406C OWN 67632E9C (008) X NLCK 10 [67710f70,67710f70] 67F440F0 WAT 6763380C (009) NLCK SX 425 [67f4417c,67710f78] 67F44174 WAT 6763417C (010) NLCK SS 8 [67f44200,67f440f8] 67F441F8 WAT 6763545C (012) NLCK SX 3 [67710f78,67f4417c] !Below the dotted lines are 4 sessions' lock waiting info. Session 8 is the !owner holding an exclusive lock on this TM resource. Waiters are sessions !9, 10 and 12 in this order. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Sequence of events: Session 8 did lock table t in exclusive mode. Session 9 did insert into t. Session 10 did alter index t_i (an index on t) rebuild online. Session 12 did another insert into t. When session 8 rolled back, session 9's insert succeeded. Interestingly, when session 9 rolled back, session 12, not 10, succeeded, immediately followed by session 10's error. It may be because 10's exclusive lock at the finishing moment of index online rebuild failed.