oracle错误1924,对于drop procedure 时hang住的问题

对于drop procedure 时hang住的问题

问题：在drop procedure的时候发现一直没反应，查询原因

SQL> conn sys/pwd@sidas 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

内容如下：

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 : :

<0/48/20150/0x45a2b2e8/3336/No Wait>

-- <0/40/11993/0x45a29c68/3284/library cache lock>

Other chains found:

Chain 2 : :

<0/8/1/0x45a1ca68/2796/wakeup time manager>

Chain 3 : :

<0/26/2234/0x45a2dc28/3200/No Wait>

Chain 4 : :

<0/29/2338/0x45a25528/672/db file sequential read>

Chain 5 : :

<0/30/23502/0x45a23728/3224/db file sequential read>

Chain 6 : :

<0/64/22233/0x45a1f3a8/1892/No Wait>

Chain 7 : :

<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

====================

发现是如下出现问题：

Chain 1 : :

<0/48/20150/0x45a2b2e8/3336/No Wait>

-- <0/40/11993/0x45a29c68/3284/library cache lock>

当前drop的session为40，然后查询48的session，居然是前面做重编译时候未成功退出的一个SESSION

然后orakill 这个session后正常drop完成