問題：在drop procedure的時候發(fā)現(xiàn)一直沒反應(yīng)，查詢原因
SQL> conn sys/pwd@sid as sysdba
已連接。
SQL> oradebug username
ORA-00070: 命令gdyf無效
SQL> oradebug setmypid
已處理的語句
SQL> oradebug hanganalyze 3
Hang Analysis in d:\oracle\admin\oracle\udump\oracle_ora_3200.trc
內(nèi)容如下：
Dump file d:\oracle\admin\oracle\udump\oracle_ora_3200.trc
Tue Mar 18 15:39:01 2008
ORACLE V9.2.0.5.0 - Production vsnsta=0
vsnsql=12 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, 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.0 Service Pack 4, CPU type 586
Instance name: oracle
Redo thread mounted by this instance: 1
Oracle process number: 82
Windows thread id: 3200, image: ORACLE.EXE
*** SESSION ID:(26.2234) 2008-03-18 15:39:01.390
*** 2008-03-18 15:39:01.390
==============
HANG ANALYSIS:
==============
Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/48/20150/0x45a2b2e8/3336/No Wait>
-- <0/40/11993/0x45a29c68/3284/library cache lock>
Other chains found:
Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/8/1/0x45a1ca68/2796/wakeup time manager>
Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/26/2234/0x45a2dc28/3200/No Wait>
Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/29/2338/0x45a25528/672/db file sequential read>
Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/30/23502/0x45a23728/3224/db file sequential read>
Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/64/22233/0x45a1f3a8/1892/No Wait>
Chain 7 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/69/1878/0x45a1dd28/2084/No Wait>
Extra information that will be dumped at higher levels:
[level??4] :?? 1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]
[level??5] :?? 6 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level??6] :?? 1 node dumps -- [NLEAF]
[level 10] :??73 node dumps -- [IGN]
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[0]/0/1/1/0x4520da08/3160/IGN/1/2//none
[1]/0/2/1/0x45a652ac/2828/IGN/3/4//none
[2]/0/3/1/0x4520e378/1924/IGN/5/6//none
[3]/0/4/1/0x45a65c1c/3308/IGN/7/8//none
[4]/0/5/1/0x4520ece8/156/IGN/9/10//none
[5]/0/6/1/0x45a6658c/1884/IGN/11/12//none
[6]/0/7/1/0x4520f658/3064/IGN/13/14//none
[7]/0/8/1/0x45a66efc/2796/SINGLE_NODE/15/16//none
[8]/0/9/18325/0x4520ffc8/2496/IGN/17/18//none
[12]/0/13/17667/0x452112a8/1928/IGN/19/20//none
[13]/0/14/18771/0x45a68b4c/816/IGN/21/22//none
[14]/0/15/9177/0x45211c18/2816/IGN/23/24//none
[15]/0/16/16965/0x45a694bc/3108/IGN/25/26//none
[16]/0/17/19671/0x45212588/2724/IGN/27/28//none
[17]/0/18/25029/0x45a69e2c/3272/IGN/29/30//none
[18]/0/19/11291/0x45212ef8/3080/IGN/31/32//none
[19]/0/20/25825/0x45a6a79c/3164/IGN/33/34//none
[20]/0/21/26486/0x45213868/448/IGN/35/36//none
[21]/0/22/21584/0x45a6b10c/2316/IGN/37/38//none
[22]/0/23/37646/0x452141d8/3340/IGN/39/40//none
[23]/0/24/5364/0x45a6ba7c/1468/IGN/41/42//none
[24]/0/25/33844/0x45214b48/2920/IGN/43/44//none
[25]/0/26/2234/0x45a6c3ec/3200/SINGLE_NODE_NW/45/46//none
[26]/0/27/28532/0x452154b8/2848/IGN/47/48//none
[28]/0/29/2338/0x45215e28/672/SINGLE_NODE/49/50//none
[29]/0/30/23502/0x45a6d6cc/3224/SINGLE_NODE/51/52//none
[31]/0/32/17993/0x45a6e03c/2580/IGN/53/54//none
[32]/0/33/21925/0x45217108/2584/IGN/55/56//none
[33]/0/34/20841/0x45a6e9ac/1316/IGN/57/58//none
[34]/0/35/19307/0x45217a78/3076/IGN/59/60//none
[35]/0/36/19269/0x45a6f31c/3092/IGN/61/62//none
[36]/0/37/11711/0x452183e8/2908/IGN/63/64//none
[37]/0/38/29651/0x45a6fc8c/2156/IGN/65/66//none
[38]/0/39/13933/0x45218d58/3332/IGN/67/68//none
[39]/0/40/11993/0x45a705fc/3284/NLEAF/69/72/[47]/none
[40]/0/41/23604/0x452196c8/3280/IGN/73/74//none
[41]/0/42/30238/0x45a70f6c/3312/IGN/75/76//none
[43]/0/44/15405/0x45a718dc/2528/IGN/77/78//none
[45]/0/46/33109/0x45a7224c/1920/IGN/79/80//none
[47]/0/48/20150/0x45a72bbc/3336/LEAF_NW/70/71//39
[48]/0/49/30884/0x4521bc88/2436/IGN/81/82//none
[49]/0/50/17150/0x45a7352c/2620/IGN/83/84//none
[50]/0/51/24541/0x4521c5f8/2376/IGN/85/86//none
[51]/0/52/20825/0x45a73e9c/2924/IGN/87/88//none
[52]/0/53/8516/0x4521cf68/764/IGN/89/90//none
[53]/0/54/16868/0x45a7480c/3320/IGN/91/92//none
[55]/0/56/14359/0x45a7517c/3356/IGN/93/94//none
[56]/0/57/32976/0x4521e248/2624/IGN/95/96//none
[57]/0/58/30428/0x45a75aec/3212/IGN/97/98//none
[58]/0/59/12175/0x4521ebb8/3268/IGN/99/100//none
[59]/0/60/15084/0x45a7645c/3032/IGN/101/102//none
[60]/0/61/31222/0x4521f528/1912/IGN/103/104//none
[61]/0/62/20108/0x45a76dcc/2564/IGN/105/106//none
[62]/0/63/32235/0x4521fe98/3376/IGN/107/108//none
[63]/0/64/22233/0x45a7773c/1892/SINGLE_NODE_NW/109/110//none
[64]/0/65/27250/0x45220808/1948/IGN/111/112//none
[65]/0/66/33424/0x45a780ac/3252/IGN/113/114//none
[66]/0/67/41625/0x45221178/3360/IGN/115/116//none
[67]/0/68/33495/0x45a78a1c/2420/IGN/117/118//none
[68]/0/69/1878/0x45221ae8/2084/SINGLE_NODE_NW/119/120//none
[70]/0/71/7937/0x45222458/3380/IGN/121/122//none
[71]/0/72/18116/0x45a79cfc/1448/IGN/123/124//none
[72]/0/73/37603/0x45222dc8/2936/IGN/125/126//none
[73]/0/74/23965/0x45a7a66c/2480/IGN/127/128//none
[74]/0/75/7067/0x45223738/3300/IGN/129/130//none
[75]/0/76/8541/0x45a7afdc/1068/IGN/131/132//none
[76]/0/77/9178/0x452240a8/2360/IGN/133/134//none
[77]/0/78/25889/0x45a7b94c/2864/IGN/135/136//none
[78]/0/79/11006/0x45224a18/2996/IGN/137/138//none
[79]/0/80/19934/0x45a7c2bc/3384/IGN/139/140//none
[80]/0/81/24305/0x45225388/3136/IGN/141/142//none
[81]/0/82/27089/0x45a7cc2c/2744/IGN/143/144//none
[82]/0/83/15792/0x45225cf8/3236/IGN/145/146//none
[83]/0/84/19887/0x45a7d59c/2788/IGN/147/148//none
[86]/0/87/20152/0x45226fd8/2284/IGN/149/150//none
[87]/0/88/7617/0x45a7e87c/2776/IGN/151/152//none
[88]/0/89/33192/0x45227948/3248/IGN/153/154//none
[89]/0/90/22038/0x45a7f1ec/2652/IGN/155/156//none
[90]/0/91/31249/0x452282b8/2852/IGN/157/158//none
[91]/0/92/15647/0x45a7fb5c/3088/IGN/159/160//none
[92]/0/93/3053/0x45228c28/3100/IGN/161/162//none
====================
END OF HANG ANALYSIS
====================


發(fā)現(xiàn)是如下出現(xiàn)問題：
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
????<0/48/20150/0x45a2b2e8/3336/No Wait>
-- <0/40/11993/0x45a29c68/3284/library cache lock>

當(dāng)前drop的session為40，然后查詢48的session，居然是前面做重編譯時候未成功退出的一個SESSION
然后orakill 這個session后正常drop完成