oradebug-hanganalyze紧急处理锁问题

oradebug hanganalyze紧急处理锁问题如下模拟及处理锁的方法：环境：windows2003server+oracle10201(Unix或linux同理)打开一个窗口A：此时不提交commit模拟死锁。查看当前的sid，如下：154再打开一个窗口B：此时HUNG住不动了，一直在等待。(由于窗口A执行update后没有commit)回到窗口A，查询SID：141就是窗口B的连接sid。然后打开窗口C，我们通过oradebug hanganalyze 3命令查看死锁情况：打开文件查看内容：Dump file d:oracleproduct10.2.0adminjycudumpjyc_ora_9112.trcThu Nov 10 09:48:36 2011ORACLE V10.2.0.1.0 - Production vsnsta=0vsnsql=14 vsnxtr=3Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsWindows Server 2003 Version V5.2 Service Pack 2CPU : 2 - type 586, 1 Physical CoresProcess Affinity : 0x00000000Memory (Avail/Total): Ph:2156M/4059M, Ph+PgF:7616M/9890M, VA:1296M/2047MInstance name: jycRedo thread mounted by this instance: 1Oracle process number: 20Windows thread id: 9112, image: ORACLE.EXE (SHAD)* SERVICE NAME:(SYS$USERS) 2011-11-10 09:48:36.421* SESSION ID:(142.24) 2011-11-10 09:48:36.421* 2011-11-10 09:48:36.421=HANG ANALYSIS:=Open chains found:Chain 1 : : - Other chains found:Chain 2 : : Chain 3 : : Chain 4 : : Chain 5 : : Chain 6 : : Extra information that will be dumped at higher levels:level 4 : 1 node dumps - REMOTE_WT LEAF LEAF_NW level 5 : 5 node dumps - SINGLE_NODE SINGLE_NODE_NW IGN_DMP level 6 : 1 node dumps - NLEAF level 10 : 11 node dumps - IGN State of nodes(nodenum/cnode/sid/sess_srno/session/ospid/state/start/finish/adjlist/predecessor):140/0/141/14/0x3432a014/9720/NLEAF/1/4/153/none141/0/142/24/0x3432b2dc/9112/SINGLE_NODE_NW/5/6/none145/0/146/2/0x3432fdfc/10724/SINGLE_NODE/7/8/none148/0/149/32/0x34333654/8452/SINGLE_NODE/9/10/none152/0/153/4/0x34338174/10600/SINGLE_NODE/11/12/none153/0/154/3/0x3433943c/9544/LEAF/2/3/140157/0/158/4/0x3433df5c/8736/SINGLE_NODE/13/14/none159/0/160/1/0x343404ec/11228/IGN/15/16/none160/0/161/1/0x343417b4/11836/IGN/17/18/none161/0/162/1/0x34342a7c/11656/IGN/19/20/none162/0/163/1/0x34343d44/9420/IGN/21/22/none163/0/164/1/0x3434500c/5700/IGN/23/24/none164/0/165/1/0x343462d4/9476/IGN/25/26/none165/0/166/1/0x3434759c/4908/IGN/27/28/none166/0/167/1/0x34348864/12156/IGN/29/30/none167/0/168/1/0x34349b2c/8496/IGN/31/32/none168/0/169/1/0x3434adf4/10732/IGN/33/34/none169/0/170/1/0x3434c0bc/11856/IGN/35/36/none=END OF HANG ANALYSIS=从上面可以看到sid 154锁住了141，所以只要将154 kill掉，141就可以继续执行了。Kill掉154的session可以有两种办法：1. alter system kill session sid,serial#;SQL select s.sid,s.serial#,p.spid from v$process p,v$session s where p.addr=s.paddr and s.username=JYC; SID SERIAL# SPID- - - 141 14 9720 154 3 9544SQL alter system kill session 154,3;系统已更改。2. 在DOC窗口下执行：orakill ORACLE_SID ospidorakill JYC 9544如果是Unix或者linux下，那么就用kill -9 ospid执行kill操作后：回到窗口A看到：在窗口B看到成功删除：术语解释：sid是 Session IDsess_srno是serial#proc_ptr是Process Pointerospid 是OS Process IDcnode是Node Id，Oracle9i才用Nodenum是hanganalyze自己为了记录这些会话而定制的编号，从0开始排起。State 是node的状态Adjlist是临近的node(通常代表一个blocker node)Predecessor是Predecessor node ,通常代表一个 waiter nodeIN_HANG：这表示该node处于死锁状态，通常还有其他node（blocker）也处于该状态LEAF/LEAF_NW：该node通常是blocker。通过条目的”predecessor”列可以判断这个node是否是blocker。LEAF说明该NODE没有等待其他资源，而LEAF_NW则可能是没有等待其他资源或者是在使用CPU.如下的实例说明了node16阻塞了node19的资源：nodenum/cnode/sid/sess_srno/session/ospid/state/start/finish/adjlist/predecessor 16/0/17/154/0x24617be0/26800/LEAF/29/30/19 19/0/20/13/0x24619830/26791/NLEAF/33/34/16/186 NL